[jmull]

> it also makes it easier for compilers/query planners to understand what you're trying to do

Hopefully that's not true. SQL's a declarative language, where you describe what you want and the system figures out how to do it. If you describe exactly the same thing using two different syntaxes, the system shouldn't really do anything different. That just makes the programmer's job harder.

[sgarland]

Ideally, but not always true. For various versions of MySQL and Postgres, the planner will infer when a semi/anti-join can replace a WHERE [NOT] IN clause, but not always. IIRC there are still edge cases where neither one picks up on it, and so it’s always safer (and much more clear to the reader) to explicitly use WHERE [NOT] EXISTS if that’s what you want.