EXISTS vs IN - semantic difference

Question

Accepted Answer

EXISTS short-circuits: the moment one matching row is found, it returns TRUE and moves on. It never cares about the actual values in the SELECT list, only that a row exists. IN materializes the subquery result into a set of values first, then checks each outer row against that set. For large subquery results this can be memory-intensive. EXISTS is generally preferred for correlated checks on large datasets; IN is clean and readable for small, uncorrelated value lists. Both can use indexes effectively when written well.