SQL Interview Preparation—Intermediate Level/

...

/

Answer: UNION Set Operator

Answer: UNION Set Operator

Find a detailed explanation of using UNION in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to use UNION to combine result sets */
SELECT   EmpName, ProjectName
FROM     Employees AS E
JOIN     Projects AS P ON E.EmpID = P.EmpID
UNION
SELECT   'No Employee Assigned' AS EmpName, ProjectName
FROM     Projects
WHERE    EmpID IS NULL
ORDER BY EmpName DESC, ProjectName ASC;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT query selects EmpName and ProjectName from Employees and Projects, respectively.

  • Line 3: The data is retrieved from the Employees table. We use AS to set an alias for the tables.

  • Line 4: A JOIN operation is applied with Projects to combine the data based on the EmpID column.

  • Line 5: The UNION keyword is used to combine the results of two queries.

  • Line 6: This part selects projects that currently have no employee linked to them and it renames ‘No Employee Assigned’ in EmpName.

  • Line 7: The data is retrieved from the Projects table.

  • Line 8: The WHERE clause ...