How do EXISTS and IN differ in SQL Server interviews?

Short answer: EXISTS tests for at least one matching row and short-circuits. IN compares to a list/set. NOT IN fails if the list contains NULL. Prefer EXISTS/NOT EXISTS for anti-semi-joins.

If you remember only one rule: beware NOT IN + NULL.