EXISTS and NOT EXISTS
What You'll Learn
You'll learn how to test whether a subquery returns any rows at all using EXISTS, and how this differs from checking membership with IN.
The Core Idea
EXISTS is a logical operator that returns true if a subquery produces at least one row—regardless of what data those rows contain. It doesn't care about the values; it only checks: "Does this subquery find anything?"
NOT EXISTS is the opposite: it returns true only if the subquery finds nothing.
Think of it like asking "Is there anyone in the room?" rather than "Who is in the room?" You don't need names; you just need a yes/no answer.
How EXISTS Differs from IN
While IN checks if a specific value appears in a subquery's result set:
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Boston');
EXISTS simply checks if any matching row exists:
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.city = 'Boston');
Notice the SELECT 1 inside the EXISTS subquery? The actual column selected doesn't matter—EXISTS only cares whether the subquery returns rows.
Using NOT EXISTS
NOT EXISTS is perfect for finding records that don't have a match:
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
This finds customers with no orders—checking "Is there zero evidence of orders for this customer?"
Performance Note
EXISTS often performs better than IN because it can stop searching as soon as it finds the first matching row, while IN may need to build the entire result set.
Key Takeaway: EXISTS tests whether a subquery finds any rows (not what they contain), making it ideal for existence checks and often more efficient than IN for correlated queries.