Abstract / Summary

Find employees whose manager is no longer with the company using given employee and manager tables.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 49% (800k+ submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The provided SQL statement retrieves identifiers of employees whose compensation falls below a defined threshold, who report to a manager, and whose manager does not appear as an employee in the same table. The result set is sorted by the employee identifier to give a deterministic ordering.

Query Structure

The query is composed of four logical sections: the column list, the source table, a series of filtering predicates, a correlated sub-query, and a final ordering clause. Each section contributes to narrowing the data set before the final sort is applied.

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

Key Steps

The first step selects the employee_id column from the Employees table. This limits the output to a single identifier per qualifying row.

SELECT employee_id
FROM Employees

The second step applies a salary filter that excludes any employee earning 30,000 or more. This predicate eliminates high-earning staff early in the execution plan.

WHERE salary < 30000

The third step ensures that only employees with an assigned manager are considered. By checking that manager_id is not null, the query discards any top-level or orphan records.

AND manager_id IS NOT NULL

The fourth step introduces a sub-query that returns all employee_id values present in the table. The outer query then excludes any rows where the manager_id appears in that list, effectively selecting employees whose manager does not exist as an employee record.

AND manager_id NOT IN (
    SELECT employee_id
    FROM Employees
)

The final step orders the remaining identifiers in ascending order, providing a predictable sequence for downstream consumption.

ORDER BY employee_id

Edge Cases Handled

When an employee’s salary column contains a null value, the < 30000 comparison evaluates to unknown, and the row is automatically filtered out, ensuring that only rows with a defined salary are examined. The explicit manager_id IS NOT NULL predicate prevents rows with missing manager references from being mistakenly included, even if the manager identifier would otherwise satisfy the NOT IN condition. If the sub-query returns an empty result set-meaning no employee identifiers exist-the NOT IN predicate evaluates to true for all non-null manager identifiers, allowing the outer filter to pass those rows. Conversely, if the sub-query yields a list that includes the manager identifier, the corresponding employee is excluded, guaranteeing that only employees whose managers are absent from the employee roster are returned.

Performance Optimizations

The query benefits from index usage on the columns involved in the predicates. An index on salary enables rapid location of rows below the 30,000 threshold. An index on manager_id supports the null check and the anti-join operation implied by the NOT IN sub-query. The sub-query itself can be transformed by the optimizer into a semi-join or anti-join, allowing the database engine to avoid materializing the full list of employee identifiers and instead perform a set-based exclusion. The ordering clause operates on a reduced result set after all filters have been applied, minimizing the amount of data that must be sorted. Together, these factors lead to an execution plan that filters early, leverages index lookups, and postpones sorting until the final, smallest possible dataset is produced.

Imagine a giant box filled with cards for every person who works at a big company. Each card has a special number on it to show who the person is. This code is like asking a robot to go through that box and find only the people who follow three specific rules.

First, the robot looks for everyone who earns less money than a certain amount. Second, it checks to see if the person has a boss, because the robot ignores anyone who does not have a manager listed. Third, it plays a game of hide and seek to make sure the name of that manager is not actually on any of the other employee cards in the box.

When the robot finds a person who fits all those rules at the same time, it writes their card number down. Finally, the robot puts all those numbers in order from the smallest one to the biggest one so everything looks nice and tidy.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	Progiez		C++	Java	Python
github	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
Progiez	C++, Java, Python
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality