Employees With Missing Information - Problem
You're tasked with identifying employees with missing information in a company's database system. The company maintains employee data across two separate tables:
- Employees table: Contains employee IDs and their names
- Salaries table: Contains employee IDs and their salary information
An employee has missing information if either:
- Their name is missing (they exist in Salaries but not in Employees)
- Their salary is missing (they exist in Employees but not in Salaries)
Your goal is to write a SQL query that finds all employee IDs with missing information and returns them in ascending order.
Example: If employee #123 has a name but no salary record, or employee #456 has a salary but no name record, both should be included in the result.
Input & Output
example_1.sql — Basic Case
$
Input:
Employees table:\n| employee_id | name |\n|-------------|-------|\n| 2 | Crew |\n| 4 | Haven |\n| 5 | Kristian |\n\nSalaries table:\n| employee_id | salary |\n|-------------|--------|\n| 5 | 76071 |\n| 1 | 22517 |\n| 4 | 63539 |
›
Output:
| employee_id |\n|-------------|\n| 1 |\n| 2 |
💡 Note:
Employee 1 has a salary but no name record (missing from Employees table). Employee 2 has a name but no salary record (missing from Salaries table). Both have incomplete information.
example_2.sql — All Complete
$
Input:
Employees table:\n| employee_id | name |\n|-------------|------|\n| 1 | John |\n| 2 | Jane |\n\nSalaries table:\n| employee_id | salary |\n|-------------|--------|\n| 1 | 50000 |\n| 2 | 60000 |
›
Output:
| employee_id |\n|-------------|\n(Empty result)
💡 Note:
All employees have both name and salary information, so no employee has missing information.
example_3.sql — Only Names or Salaries
$
Input:
Employees table:\n| employee_id | name |\n|-------------|------|\n| 1 | Alice |\n| 2 | Bob |\n\nSalaries table:\n| employee_id | salary |\n|-------------|--------|\n| 3 | 70000 |\n| 4 | 80000 |
›
Output:
| employee_id |\n|-------------|\n| 1 |\n| 2 |\n| 3 |\n| 4 |
💡 Note:
No employee has complete information. Employees 1 and 2 have names but no salaries, while employees 3 and 4 have salaries but no names.
Constraints
- 1 ≤ employee_id ≤ 105
- 1 ≤ name.length ≤ 20
- 1 ≤ salary ≤ 106
- employee_id is unique in both tables
- name consists of uppercase and lowercase English letters
Visualization
Tap to expand
Understanding the Visualization
1
Identify All Employees
Gather all employee IDs from both the name registry and payroll system
2
Match Records
Use FULL OUTER JOIN to match employees across both systems
3
Find Gaps
Identify employees with NULL values in either name or salary columns
4
Report Missing
Return the employee IDs with incomplete information in ascending order
Key Takeaway
🎯 Key Insight: FULL OUTER JOIN reveals all employees from both systems, and NULL values indicate missing information that needs attention.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code