Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: UNION ALL Set Operator

Explore how to use the UNION ALL operator to merge results from multiple SQL queries while preserving duplicates. Understand joins, aliases, and filtering conditions to combine employee data, skills, and projects efficiently.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to use UNION ALL to combine result sets */
SELECT    e.EmpID, e.EmpName, s.SkillName, p.ProjectName 
FROM      Employees e 
LEFT JOIN Skills s ON e.EmpID = s.EmpID 
LEFT JOIN Projects p ON e.EmpID = p.EmpID OR s.SkillID = p.SkillID 
UNION ALL 
SELECT    e.EmpID, e.EmpName, NULL AS SkillName, NULL AS ProjectName 
FROM      Employees e 
WHERE     e.EmpID NOT IN (SELECT DISTINCT EmpID FROM Skills) 
AND       e.EmpID NOT IN (SELECT DISTINCT EmpID FROM Projects)
ORDER BY  EmpName DESC;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The first SELECT query selects EmpID, EmpName, SkillName, and ProjectName. The aliases used are for Employees, Skills, and Projects.

  • Line 3: The data is retrieved from the Employees table. We use alias for the table.

  • Line 4: The LEFT JOIN operation is applied with Skills to combine the data based on the EmpID column present in both the Employees and Skills tables.

  • Line 5: The LEFT JOIN operation is applied with Projects to combine the data based on the EmpID or SkillID.

  • Line 6: The UNION ALL keyword is used to combine the results of two queries, including duplicates.

  • Line 7: The ...