EXISTS and NOT EXISTS test whether a subquery produces at least one matching row. They are especially useful for relationship questions such as finding customers with purchases or customers who have never purchased. Unlike a regular join, EXISTS does not multiply the outer row just because several matching child rows exist.
Key Takeaways at a Glance:
- •EXISTS tests whether a subquery returns at least 1 row; it returns a boolean TRUE or FALSE immediately upon finding the first match (short-circuit).
- •Because EXISTS only checks row existence, 'SELECT 1' or 'SELECT *' inside EXISTS has identical performance.
- •NOT EXISTS is completely NULL-safe, whereas 'NOT IN' evaluates to UNKNOWN and fails completely if the subquery returns even one NULL value.
- •Anti-Join patterns: NOT EXISTS and 'LEFT JOIN ... WHERE right.key IS NULL' are the two industry-standard ways to find non-matching records.
In Simple Words (The Mental Model)
EXISTS asks a simple Yes/No question: 'Does at least one matching record exist?' As soon as the database finds even a single matching row, it stops looking and says 'YES' (TRUE). It doesn't count how many there are, which makes it super fast.
Real-World Analogy
Think of checking if a library has books on 'Quantum Physics'. You don't need the librarian to carry all 50 quantum books to your desk and count them. You just need them to glance at the shelf, see one book with that title, and immediately say 'Yes, we have it' (EXISTS short-circuit).