Correlated EXISTS and NOT EXISTS
The robust anti-join alternative that handles NULLs correctly.
EXISTS Tests for Presence
EXISTS takes a subquery and returns TRUE as soon as that subquery yields at least one row, otherwise FALSE. It never returns the rows themselves.
With a correlated subquery inside, EXISTS becomes a per-outer-row presence test: "does a matching row exist for this outer row?"
Because it short-circuits on the first match, it does not care how many rows match. That semantics detail is a favorite interview point.
A Basic Correlated EXISTS
Find customers who have placed at least one order. The inner query is correlated through o.customer_id = c.customer_id.
For each customer, EXISTS asks: is there any order for this customer? If yes, keep the customer.
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);All lessons in this course
- Anatomy of a Correlated Subquery
- Per-Group Aggregates Without GROUP BY
- Correlated EXISTS and NOT EXISTS
- Rewriting Correlated Subqueries as Joins