Skip to main content

LeetCode 1965. Employees With Missing Information SQL Solution

Problem​

LeetCode SQL Problem

  1. Employees With Missing Information

Employees table

employee_idname
2Crew
4Haven
5Kristian

Salaries table

employee_idsalary
576071
122517
463539

Solution - Left Join + Right Join + Union​

Find Employees With Missing Information
SELECT E.employee_id AS employee_id
FROM Employees AS E
LEFT JOIN Salaries AS S ON E.employee_id = S.employee_id
WHERE S.employee_id IS NULL

UNION

SELECT S.employee_id
FROM Employees AS E
RIGHT JOIN Salaries AS S ON E.employee_id = S.employee_id
WHERE E.employee_id IS NULL
ORDER BY employee_id

The above SQL query combines the results of two queries using the UNION operator, then orders the results by employee ID.

Let's break the SQL down step by step:

  1. SELECT E.employee_id AS employee_id: This selects the employee IDs of employees from the Employees table and assigns them an alias of "employee_id".

  2. FROM Employees AS E: This specifies the table we're selecting from, which is the Employees table, and assigns it an alias of "E".

  3. LEFT JOIN Salaries AS S ON E.employee_id = S.employee_id: This joins the Employees table to the Salaries table using a left join. The join condition is that the employee ID in table E matches the employee ID in table S. This will create a table where each row represents an employee, and the corresponding row (if any) from the Salaries table where that employee's employee ID matches the employee ID in Salaries table.

  4. WHERE S.employee_id IS NULL: This filters the results to only include employee IDs that do not have a corresponding row in the Salaries table.

  5. UNION: This operator combines the results of this query with the results of the second query using set union. This means that any duplicates are removed, and the final result set contains all unique values.

  6. SELECT S.employee_id: This selects the employee IDs of employees from the Salaries table.

  7. FROM Employees AS E: This specifies the table we're selecting from, which is the Employees table, and assigns it an alias of "E".

  8. RIGHT JOIN Salaries AS S ON E.employee_id = S.employee_id: This joins the Employees table to the Salaries table using a right join. The join condition is that the employee ID in table E matches the employee ID in table S. This will create a table where each row represents an employee in the Salaries table, and the corresponding row (if any) from the Employees table where that employee's employee ID matches the employee ID in Employees table.

  9. WHERE E.employee_id IS NULL: This filters the results to only include employee IDs that do not have a corresponding row in the Employees table.

  10. ORDER BY employee_id: This orders the final result set by the employee ID column.

The overall effect of this query is to find all employee IDs that do not have a corresponding row in the Salaries table (i.e., The employee's salary is missing), and all employee IDs that do not have a corresponding row in the Employees table (i.e., The employee's name is missing), then combine the two sets of results and order them by employee ID.

Query Output

employee_id
1
2