Employees With Missing Information - Solution & Explanation
Problem Statement
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.
Approach Overview
Problem Overview: Two database tables store employee records separately. Employees contains employee_id and name, while Salaries stores employee_id and salary. Some employees appear in only one table. Your task is to return all employee_id values that are missing information in either table and sort the result in ascending order.
Approach 1: SQL JOIN and NULL Check (O(n + m) time, O(1) extra space)
The most common solution uses LEFT JOIN operations to detect unmatched rows. Join Employees with Salaries on employee_id and filter rows where the salary record is NULL. Do the reverse join to find salaries that do not have a matching employee record. Combine both result sets with UNION. The key insight: unmatched rows in joins produce NULL values, which directly reveal missing information. This approach works efficiently because the database performs indexed join operations internally. When working with relational datasets, this pattern appears frequently in SQL and database interview questions.
Approach 2: SQL UNION with NOT IN / NOT EXISTS (O(n + m) time, O(1) extra space)
Another clean SQL solution checks membership explicitly. Select employee IDs from Employees that do not appear in Salaries, then union them with IDs from Salaries that do not appear in Employees. Both queries run independently and the UNION merges the results while removing duplicates. The core operation is a set difference between the two tables. This approach is easy to read and commonly used when joins would make the query harder to follow.
Approach 3: SQL FULL OUTER JOIN (O(n + m) time, O(1) extra space)
A FULL OUTER JOIN directly exposes rows that exist in only one table. When joining Employees and Salaries, rows missing from either side produce NULL columns. Filtering where either the employee record or salary record is NULL returns exactly the employees with incomplete data. This is often the most expressive solution conceptually, though some SQL dialects do not support full outer joins. ORM-based implementations (such as SQLAlchemy or Entity Framework) typically translate this pattern cleanly.
Approach 4: Hashing (O(n + m) time, O(n + m) space)
Outside SQL environments, the problem becomes a simple set comparison. Insert all employee_id values from the first dataset into a hash set, then iterate through the second dataset. IDs missing in the set indicate salaries without employee records. Repeat the process in the opposite direction. Hash lookups run in constant time, making the overall complexity linear. This technique mirrors classic hashing interview problems where you detect elements present in one collection but absent in another.
Recommended for interviews: The SQL JOIN + NULL filtering approach is the most expected answer for database interviews. It demonstrates a solid understanding of relational joins and how missing matches appear as NULL. Mentioning the FULL OUTER JOIN variant shows deeper SQL knowledge, while the hashing approach demonstrates how the same logic translates to application code.
Approach 1: Approach 1: SQL JOIN and NULL Check
This approach utilizes SQL's JOIN operations to identify missing information. We can use a FULL OUTER JOIN between the Employees and Salaries tables to ensure we capture all employee IDs, whether they appear in only one of the tables (missing from the other) or in both.
After joining, we will filter the results for rows where either the name or salary is NULL.
This SQL query uses a FULL OUTER JOIN to combine the data from the Employees and Salaries tables on the employee_id. The WHERE clause is used to filter out rows where either the name is NULL or the salary is NULL, indicating missing information from one of the tables.
Code
SQL
Complexity
Time Complexity: O(N + M), where N is the number of employees and M is the number of salaries, because the join operation requires a scan of both tables.
Space Complexity: O(N + M) due to the storage of the join results.
Approach 2: Approach 2: SQL UNION
This solution employs two separate queries combined with UNION to find missing records. The first query selects employee_ids from the Employees table that do not exist in the Salaries table (indicating missing salary records), and the second query selects employee_ids from the Salaries table that do not exist in the Employees table (indicating missing employee records).
This SQL script combines two subqueries using UNION: The first subquery lists employee_ids from the Employees table that do not appear in Salaries (missing salaries), and the second lists employee_ids from Salaries that do not appear in Employees (missing names). The result is returned in ascending order by employee_id.
Code
SQL
Complexity
Time Complexity: O(N * M) since each subquery potentially involves a scan of the Employees table for each entry in Salaries, and vice versa.
Space Complexity: O(N + M), primarily for the UNION operation result storage.
Approach 3: Approach 1: SQL Full Outer Join
This approach involves using a SQL full outer join to combine the Employees and Salaries tables on the employee_id column. By checking for null values in the resulting join, you can determine which employees have missing information (either name or salary).
In this approach, we establish a connection to the database and use SQLAlchemy to perform a full outer join between the Employees and Salaries tables using their employee IDs. The key here is checking for nulls in both the employee name and salary after the join. We achieve this using the left outer join and union of two conditions: when the name is missing and when the salary is missing. The union ensures all relevant IDs with missing information are captured.
Code
Python (with SQLAlchemy)
C# (with Entity Framework)
Complexity
Time Complexity: O(n + m), where n is the number of records in the Employees table and m is the number of records in the Salaries table as we need to check all employees and salaries.
Space Complexity: O(k), where k is the number of employee_ids with missing information, primarily due to the result storage.
Approach 4: Approach 2: Hashing
This approach involves using two hash maps (dictionaries) to store information about employees and salaries. By comparing the keys of these two maps, we can determine the missing information by finding the employee IDs that are not present in both maps.
This method uses dictionaries to create mappings from Employee IDs to names and salaries, respectively. As we iterate through the IDs in both dictionaries, we identify IDs present in one dictionary but not the other. These IDs are collected to find employees with missing information, and then we sort and return them.
Code
Python
JavaScript
Complexity
Time Complexity: O(n + m), where n is the size of the employees list and m is the size of the salaries list.
Space Complexity: O(n + m) due to the space used for storing the dictionaries of employees and salaries.
Approach 5: 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.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Approach 1: SQL JOIN and NULL Check | Time Complexity: O(N + M), where N is the number of employees and M is the number of salaries, because the join operation requires a scan of both tables. |
| Approach 2: SQL UNION | Time Complexity: O(N * M) since each subquery potentially involves a scan of the Employees table for each entry in Salaries, and vice versa. |
| Approach 1: SQL Full Outer Join | Time Complexity: O(n + m), where n is the number of records in the Employees table and m is the number of records in the Salaries table as we need to check all employees and salaries. |
| Approach 2: Hashing | Time Complexity: O(n + m), where n is the size of the employees list and m is the size of the salaries list. |
| Subquery + Union | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL JOIN + NULL Check | O(n + m) | O(1) | Standard SQL solution for detecting unmatched rows between tables |
| SQL UNION with NOT IN / NOT EXISTS | O(n + m) | O(1) | When you want explicit set difference queries instead of joins |
| SQL FULL OUTER JOIN | O(n + m) | O(1) | Best conceptual approach if the SQL dialect supports full outer joins |
| Hashing (Application Code) | O(n + m) | O(n + m) | When solving outside SQL using Python or JavaScript collections |
Video Solution
LeetCode 1965 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 6,640 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Employees With Missing Information easy or hard?
How to solve Employees With Missing Information in O(n)?
Employees With Missing Information Python or Java solution
What is the best approach for Employees With Missing Information?
Is Employees With Missing Information asked at Google/Amazon/Meta?
What data structure is used in Employees With Missing Information?
What is the time complexity of Employees With Missing Information?
Ready to solve this problem?
Practice Employees With Missing Information with our built-in code editor and test cases.
Practice on FleetCodeProblem Info
Table of Contents
Practice this problem
Open in Editor