Employees Whose Manager Left the Company | Leetcode 1978

This chapter marks the transition into the Subqueries module of the 50-question SQL interview series. We explore how to identify missing relational references (dangling foreign keys) using subqueries, multi-condition filtering, and set operations in MySQL.

1. Why Do We Need Subqueries? (Problem Statement & Status Quo)

In standard SQL filtering, we often compare a column value against a static literal (e.g., salary < 30000). However, real-world relational databases frequently require dynamic filtering—where the validity of a row depends on data existing within another set of rows inside the same table or across tables.

When a manager leaves a company, their record is deleted from the Employees table, but their former subordinates' rows retain the manager's ID in the manager_id column. Standard equality checks (=) or static bounds fail because we cannot hardcode which managers have departed; we must dynamically inspect the active pool of employee IDs.

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Corporate Audit

Imagine auditing an office directory where every employee reports to a manager listed in the same directory.

• Some employees have been promoted or transferred, and their managers have resigned and been removed from the directory.
• If Employee X reports to Manager Y, but a search through the active employee directory reveals no record of Y, it proves that Manager Y has left the company.
• To find these abandoned reports, you first compile a master list of all currently active employee IDs, and then flag any employee whose manager_id does not appear anywhere in that master list.

3. Database Schema & Dataset Overview

We are given a single table named Employees with the following structure:

4. Step-by-Step Business Logic Breakdown

To solve LeetCode 1978, our query must simultaneously enforce two distinct business rules:

1. Salary Constraint: The employee's salary must be strictly less than $30,000 (salary < 30000).
2. Manager Departure Constraint: The manager must no longer exist in the Employees table (manager_id NOT IN (SELECT employee_id FROM Employees)).
3. Ordering Constraint: The final result set must be sorted in ascending order by employee_id (ORDER BY employee_id).

5. Walkthrough of Example Data Execution

Let us trace a sample execution to see how rows are filtered out step-by-step.

Sample Input Table (Employees)

Step 5.1: Applying the Salary Condition (salary < 30000)

Filtering out employees earning $30,000 or more:

• Mila (60301) -> Removed
• Anton (55840) -> Removed
• John (50945) -> Removed
• Jalil (25861) -> Kept (< 30000)
• Kalel (21241) -> Kept (< 30000)
• Josian (28476) -> Kept (< 30000)

Step 5.2: Applying the Manager Departure Condition (manager_id NOT IN (...))

Now we evaluate remaining candidates against active employee IDs:

• Kalel (employee_id = 1): Has manager_id = 11. Looking up 11 in the employee_id column shows Jalil (11) exists. Therefore, Kalel's manager has not left. Kalel is excluded.
• Josian (employee_id = 6): Has manager_id = 7. Looking up 7 in the employee_id column reveals no match (manager 7 was deleted). Therefore, Josian's manager has left. Josian is kept.
• Jalil (employee_id = 11): Has manager_id = 6. Looking up 6 in the employee_id column reveals Josian (6) exists. Therefore, Jalil's manager has not left. Jalil is excluded.

(Wait, let's re-verify Josian: Josian's salary is 28476 which is < 30000, and his manager is 7, which does not exist in the table. Hence, Josian qualifies!)

6. Complete SQL Query Implementation

Here is the clean, production-ready SQL query:

SELECT employee_id
FROM Employees
WHERE salary < 30000
  AND manager_id NOT IN (
      SELECT employee_id 
      FROM Employees
  )
ORDER BY employee_id;

7. Execution Flow & Architecture Walkthrough

8. Real-World Practical Pitfalls & Edge Cases

When writing queries involving NOT IN with subqueries, database engineers must watch out for critical SQL pitfalls:

• The NULL Trap in NOT IN: If a subquery returns even a single NULL value (e.g., if manager_id or employee_id contained unhandled NULLs in certain database engines), the entire NOT IN evaluation evaluates to UNKNOWN, causing the outer query to return zero rows. In LeetCode 1978, table constraints handle primary keys, but caution is required in production schemas.
• Alternative Approach (LEFT JOIN): This problem can also be solved using a LEFT JOIN by checking where the right-side table's manager ID evaluates to NULL:
  SQL

  Copy

  SELECT e1.employee_id
  FROM Employees e1
  LEFT JOIN Employees e2 ON e1.manager_id = e2.employee_id
  WHERE e1.salary < 30000
    AND e2.employee_id IS NULL
    AND e1.manager_id IS NOT NULL;
  

9. Summary and Key Takeaways

• Subqueries for Dynamic Filtering: Use subqueries when criteria depend on dynamic sets derived from the same or related tables rather than static parameters.
• NOT IN Operator: Essential for checking the absence of a value within a subquery result set (dangling foreign key detection).
• Multi-Condition Filtering: Combine row-level attributes (salary < 30000) with relational existence checks (manager_id NOT IN (...)) using the AND operator.
• Deterministic Ordering: Always conclude reporting queries with ORDER BY clauses to ensure predictable, testable output sequences.
• Handling Deleted Entities: Relational integrity loss (deleted parent records with surviving child references) can be effectively identified by cross-referencing child foreign keys against active primary keys.