Browse Teach Curate

EXISTS Is a Yes-or-No Test That Can Stop at the First Matching Row

EXISTS (SELECT ...) is true as soon as the inner query produces one row, so the engine is allowed to stop looking right there instead of finishing the subquery. That makes it the cheapest way to ask does this customer have any order, and it never multiplies rows the way a join does. It also handles NULLs sensibly, which is why NOT EXISTS is the safe replacement for NOT IN.

Questions this Concept answers

Why is `NOT EXISTS` the safer replacement for `NOT IN` over a nullable column?

jeremy Video

NOT EXISTS Correlated Subquery Versus LEFT JOIN for Finding Unmatched Rows in T-SQL

The anti-join — selecting rows of one table that have no matching row in another — can be expressed either as an outer join filtered on a null on the unmatched side, or as a `NOT EXISTS` correlated s…