exists

NOT EXISTS: The Anti-Join Pattern

Find employees who are not managers of anyone using NOT EXISTS.

Overview

NOT EXISTS is the cleanest way to express "rows with no related row" — the anti-join. Here we find employees who do not appear as anyone's manager, i.e. individual contributors with no direct reports.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

Outer query considers each employee e.
NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id) — true only if no other employee lists e as their manager.
Result: employees with zero direct reports.

NOT EXISTS vs NOT IN

NOT IN (subquery) returns no rows if the subquery contains any NULL. NOT EXISTS has no such trap — prefer it for negative membership checks.

Variations

Find customers who have never placed a completed order:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

NULL in NOT IN. Using WHERE c.id NOT IN (SELECT customer_id …) instead of NOT EXISTS breaks when customer_id is NULL.
Self-reference confusion. The subquery aliases the same table (m) and correlates on manager_id = e.id; mixing up the aliases reverses the logic.
Assuming managers are excluded. This query returns non-managers; to get only managers, use EXISTS instead of NOT EXISTS.