Question

1965. Employees With Missing Information

中文文档

Description

Table: Employees

+-------------+---------+
| Column Name | Type  |
+-------------+---------+
| employee_id | int   |
| name    | varchar |
+-------------+---------+
employee_id is the column with unique values for this table.
Each row of this table indicates the name of the employee whose ID is employee_id.

 

Table: Salaries

+-------------+---------+
| Column Name | Type  |
+-------------+---------+
| employee_id | int   |
| salary    | int   |
+-------------+---------+
employee_id is the column with unique values for this table.
Each row of this table indicates the salary of the employee whose ID is employee_id.

 

Write a solution 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.

The result format is in the following example.

 

Example 1:

Input: 
Employees table:
+-------------+----------+
| employee_id | name   |
+-------------+----------+
| 2       | Crew   |
| 4       | Haven  |
| 5       | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5       | 76071  |
| 1       | 22517  |
| 4       | 63539  |
+-------------+--------+
Output: 
+-------------+
| employee_id |
+-------------+
| 1       |
| 2       |
+-------------+
Explanation: 
Employees 1, 2, 4, and 5 are working at this company.
The name of employee 1 is missing.
The salary of employee 2 is missing.

Solutions

Solution 1: Subquery + Union

We can first find all employee_id that are not in the Salaries table from the Employees table, and then find all employee_id that are not in the Employees table from the Salaries table. Finally, we can combine the two results using the UNION operator, and sort the result by employee_id.

# Write your MySQL query statement below
SELECT employee_id
FROM Employees
WHERE employee_id NOT IN (SELECT employee_id FROM Salaries)
UNION
SELECT employee_id
FROM Salaries
WHERE employee_id NOT IN (SELECT employee_id FROM Employees)
ORDER BY 1;