SQL MOSAIC # 1965. Employees With Missing Information1

Date 6.26.2022

Write an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if:

• The employee’s name is missing, or
• The employee’s salary is missing.

Return the result table ordered by employee_id in ascending order.

Thinking Pathway

• I need to JOIN these 2 tables. Use FULL JOIN. (INCORRECT)

e.g FROM Employees e FULL JOIN Salaries s ON e.employee_id = s.employee_id

• Use WHERE to filter out where name is null or where salary is null

Explanation

There is no FULL OUTER JOIN in MySQL.

Solution(s):

SELECT *
FROM(
SELECT e.employee_id
FROM Employees e LEFT JOIN Salaries s ON e.employee_id = s.employee_id
WHERE s.salary IS NULL
UNION
SELECT s.employee_id
FROM Employees e RIGHT JOIN Salaries s ON e.employee_id = s.employee_id
WHERE e.name IS NULL) T
ORDER BY T.employee_id

url: https://leetcode.com/problems/employees-with-missing-information/