Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: UNION Set Operator

Explore how to use the UNION set operator to combine results from multiple SQL queries. Understand key concepts like joins, aliases, and filtering conditions to manage data across tables effectively. This lesson also covers alternative methods such as CASE statements, LEFT JOIN with IFNULL, and subqueries for handling unassigned data scenarios.

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 ...