Asking Whether A Row Exists
EXISTS takes a subquery and returns true as soon as that subquery yields at least one row. It does not care about the values, only about presence, which lets the engine stop early.
Key Behaviours
- EXISTS returns true on the first matching row and short circuits.
- NOT EXISTS returns true only when the subquery finds nothing.
- The columns selected inside the subquery are irrelevant, so SELECT 1 is a common convention.
EXISTS Versus IN
- IN builds a list of values and compares against it.
- EXISTS checks presence per outer row and is usually safer with NULLs, because NOT EXISTS is not broken by a NULL the way NOT IN is.
- On large related tables EXISTS often performs as a semi join and avoids materializing a big value list.
A Practical Pattern
To list customers who placed at least one order, an EXISTS subquery correlated on customer id is both readable and efficient, and it naturally avoids the duplicate rows a join can introduce.
Key idea
EXISTS tests for the presence of any matching row and short circuits, making NOT EXISTS a NULL safe and often efficient choice over NOT IN.