EXISTS vs IN vs JOIN — which should you use and why does NOT IN break with NULLs?

Answer preview

All three answer "does a related row exist", but differently: EXISTS is a semi-join that stops at the first match, IN compares a value against a materialised list, and JOIN actually combines rows — which can duplicate the outer row when several children match.…