NOT EXISTS for Finding Missing Relationships
You'll learn how to use NOT EXISTS to find records that don't have matching rows in related tables—like identifying customers who've never placed an order.
The Core Idea
While EXISTS helps you find records that do have a match in another table, NOT EXISTS does the opposite: it identifies records that lack a corresponding relationship. This is incredibly useful for finding gaps in your data—customers without orders, products never sold, students not enrolled in any courses, etc.
Think of it like checking attendance: EXISTS finds people who showed up to at least one meeting, while NOT EXISTS finds people who never attended any meetings at all.
How NOT EXISTS Works
NOT EXISTS returns TRUE when the correlated subquery finds zero matching rows. If even one match exists, it returns FALSE and that row is filtered out.
Basic Pattern:
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
This finds all customers who have no orders in the orders table. For each customer, the subquery searches for any order with a matching customer_id. If none are found, that customer appears in the results.
Why NOT EXISTS Over NOT IN
NOT EXISTS handles NULL values more predictably than NOT IN and often performs better because it can stop searching as soon as it finds one match (to exclude the row).
Key Takeaway: Use NOT EXISTS to identify records missing expected relationships—it's the go-to pattern for finding orphaned or inactive records across related tables.