Employees With Missing Information

Difficulty: Easy | Acceptance: 73.20% | Paid: No Topics: Database

Table: Employees +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id is the primary key 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 primary key for this table. Each row of this table indicates the salary of the employee whose ID is employee_id.

Write an SQL query to report the employees having missing information. An employee is considered to have missing information if:

• The employee’s name is missing from the Employees table, or
• The employee’s salary is missing from the Salaries table.

Return the result table ordered by employee_id in ascending order.

The query result format is in the following example.

• Examples
• Constraints
• Approach 1: FULL OUTER JOIN
• Approach 2: UNION with LEFT JOIN
• Approach 3: NOT EXISTS

Examples

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 2 is in the Employees table but not in the Salaries table (salary is missing).
• Employees 1 is in the Salaries table but not in the Employees table (name is missing).
• Employees 4 and 5 are present in both tables.

Constraints

- The Employees table will have at most 100 rows.
- The Salaries table will have at most 100 rows.
- employee_id values are unique within each table.

Approach 1: FULL OUTER JOIN

Intuition Use FULL OUTER JOIN to combine both tables and filter for rows where either side has NULL values, indicating missing information.

Steps

• Perform FULL OUTER JOIN between Employees and Salaries on employee_id
• Filter where either employee_id from Employees is NULL or employee_id from Salaries is NULL
• Select the non-NULL employee_id and order by employee_id

def findMissingInformation():
    query = """
    SELECT COALESCE(e.employee_id, s.employee_id) AS employee_id
    FROM Employees e
    FULL OUTER JOIN Salaries s ON e.employee_id = s.employee_id
    WHERE e.employee_id IS NULL OR s.employee_id IS NULL
    ORDER BY employee_id
    """
    return query

# LeetCode execution
# The query above is the SQL solution
# This wrapper shows how to execute from Python
import sqlite3

def execute_query():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute(findMissingInformation())
    return cursor.fetchall()

Complexity

• Time: O(n + m) where n and m are the sizes of Employees and Salaries tables
• Space: O(n + m) for storing the joined result
• Notes: FULL OUTER JOIN is not supported in MySQL, use alternative approaches for MySQL

Approach 2: UNION with LEFT JOIN

Intuition Use two LEFT JOIN queries combined with UNION to find employees missing from either table.

Steps

• Find employees in Employees but not in Salaries using LEFT JOIN
• Find employees in Salaries but not in Employees using LEFT JOIN
• Combine results with UNION and order by employee_id

def findMissingInformation():
    query = """
    SELECT e.employee_id
    FROM Employees e
    LEFT JOIN Salaries s ON e.employee_id = s.employee_id
    WHERE s.employee_id IS NULL
    UNION
    SELECT s.employee_id
    FROM Salaries s
    LEFT JOIN Employees e ON s.employee_id = e.employee_id
    WHERE e.employee_id IS NULL
    ORDER BY employee_id
    """
    return query

# LeetCode execution
import sqlite3

def execute_query():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute(findMissingInformation())
    return cursor.fetchall()

Complexity

• Time: O(n + m) where n and m are the sizes of Employees and Salaries tables
• Space: O(n + m) for storing intermediate results
• Notes: Works on all SQL databases including MySQL

Approach 3: NOT EXISTS

Intuition Use NOT EXISTS subqueries to find employees that don’t exist in the other table.

Steps

• Find employees in Employees where employee_id doesn’t exist in Salaries
• Find employees in Salaries where employee_id doesn’t exist in Employees
• Combine results with UNION and order by employee_id

def findMissingInformation():
    query = """
    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 employee_id
    """
    return query

# LeetCode execution
import sqlite3

def execute_query():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute(findMissingInformation())
    return cursor.fetchall()

Complexity

• Time: O(n × m) in worst case due to subquery execution
• Space: O(n + m) for storing results
• Notes: Simpler to understand but may be slower on large datasets