Employees With Missing Information - Problem

You have two tables: Employees and Salaries. The Employees table contains employee IDs and names, while the Salaries table contains employee IDs and their corresponding salaries.

Write a SQL query to find all employees with missing information. An employee has missing information if either:

  • The employee's name is missing (exists in Salaries but not in Employees), or
  • The employee's salary is missing (exists in Employees but not in Salaries)

Return the result ordered by employee_id in ascending order.

Table Schema

Employees
Column Name Type Description
employee_id PK int Unique identifier for each employee
name varchar Employee's name
Primary Key: employee_id
Salaries
Column Name Type Description
employee_id PK int Unique identifier for each employee
salary int Employee's salary amount
Primary Key: employee_id

Input & Output

Example 1 — Mixed Missing Information
Input Tables:
Employees
employee_id name
2 Crew
4 Haven
5 Kristian
Salaries
employee_id salary
5 76071
1 22517
4 63539
Output:
employee_id
1
2
💡 Note:

Employee 1 has a salary (22517) but no name record in the Employees table. Employee 2 has a name (Crew) but no salary record in the Salaries table. Employees 4 and 5 have both name and salary, so they are not included in the result.
Example 2 — All Employees Missing Salary
Input Tables:
Employees
employee_id name
1 John
2 Jane
Salaries
employee_id salary
Output:
employee_id
1
2
💡 Note:

Both employees have names but no salary records exist in the Salaries table, so both employee IDs are returned as having missing information.
Example 3 — No Missing Information
Input Tables:
Employees
employee_id name
1 Alice
2 Bob
Salaries
employee_id salary
1 50000
2 60000
Output:
employee_id
💡 Note:

All employees have both name and salary information, so no employee IDs are returned as the result is empty.

Constraints

  • 1 ≤ employee_id ≤ 10^6
  • name consists of uppercase and lowercase English letters
  • 1 ≤ salary ≤ 10^6

Visualization

Tap to expand
Employees With Missing Information INPUT Employees Table employee_id name 1 Alice 2 Bob 4 Diana Salaries Table employee_id salary 1 50000 3 60000 4 70000 Find IDs with missing name OR salary ALGORITHM STEPS 1 Get all employee IDs UNION of both tables 2 Find missing names IDs in Salaries NOT in Employees 3 Find missing salaries IDs in Employees NOT in Salaries 4 Combine and sort UNION results, ORDER BY id Emp Sal Both Find IDs in outer regions FINAL RESULT Missing Name (in Salaries only) ID: 3 Missing Salary (in Employees only) ID: 2 Output (Sorted ASC) employee_id 2 3 OK - 2 rows returned Key Insight: Use UNION to combine two NOT IN subqueries: find employee_ids that exist in one table but not the other. This catches both cases: employees without salary records AND salary records without employee names. TutorialsPoint - Employees With Missing Information | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Apple 8
28.4K Views
Medium Frequency
~12 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen