EXISTS: Correlated Subquery for Membership
Find customers who have at least one completed order using EXISTS.
Overview
EXISTS(subquery) returns true if the subquery returns at least one row. A
correlated subquery references the outer query's columns, so it's re-evaluated
for each outer row. EXISTS is ideal for "has at least one related row" checks
and typically outperforms COUNT(*) > 0 or IN for this.
The Query
Store (Customers/Orders/Products)
Loading database engine...
Step-by-Step Breakdown
- Outer query iterates customers.
EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id …)— for each customer, checks whether a completed order exists.SELECT 1is conventional;EXISTSignores the selected values and only cares about row existence.
EXISTS vs IN
EXISTS stops scanning at the first match and handles NULLs gracefully;
IN can misbehave with NULL subquery values. Prefer EXISTS for
existence checks.
Variations
Find movies that have at least one critic rating above 8:
Movies (Movies/Actors/Ratings)
Loading database engine...
Common Mistakes
- Selecting columns inside EXISTS.
SELECT m.titleinside the subquery is meaningless — only existence matters; useSELECT 1. - Non-correlated subquery. Forgetting
o.customer_id = c.idmakes the subquery independent and returns all-or-nothing incorrectly. - NULL pitfalls of IN. Switching to
WHERE c.id IN (SELECT customer_id …)can drop rows ifcustomer_idis everNULL;EXISTSis safer.