Hmm, nice one, I've never seen that issue described.

That provides another reason why the following syntax is (in my opinion) preferable:

select *

from employees

left join employees as subordinates on employees.employeeid = subordinates.reportsto

where subordinates.employeeid Is Null

The main other reason I know of is performance on complex queries, where the query optimizer may sometimes evaluate the subquery (potentially passing a lot of data to the outer query) instead of doing an efficient join (I don't know the exact circumstances governing this, but I have definitely seen it happen)

Unfortunately the above syntax/approach has the disadvantage that it is less intuitive to many people 🙁

I make it a rule that subqueries (but not derived tables) are to be avoided at all costs; does anyone have a more flexible rule of thumb that can allow the use of subqueries without risk of perfromance or logic issues?