SQL Interview Preparation—Intermediate Level/

...

/

Answer: UNION ALL Set Operator

Answer: UNION ALL Set Operator

Find a detailed explanation of set operator UNION ALL.

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