Skip to content
SQLSimplified
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

Loading playground environment...

Step-by-Step Breakdown

  1. Outer query considers each employee e.
  2. NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id): true only if no other employee lists e as their manager.
  3. 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:

Loading playground environment...

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.

Related examples

EXISTS and NOT EXISTS: Testing for Related Rows
exists
Use EXISTS with a correlated subquery to find authors who have published at least one book, and NOT EXISTS for the opposite.
EXISTS: Correlated Subquery for Membership
exists
Find customers who have at least one completed order using EXISTS.
LEFT JOIN to Find Missing Relationships
join
Use LEFT JOIN plus a NULL check to find customers who have never placed an order.

Cite this resource

SQLSimplified. "NOT EXISTS: The Anti-Join Pattern Example". Available at: https://sqlsimplified.online/examples/not-exists-anti-join