• The optimizer works on a logical tree of operations. That tree is derived from the text of the statement presented. The optimizer has many built in guaranteed-safe transformations which mean that many logically equivalently written queries end up producing an identical, or trivially different, execution plan.

    The important thing to realise is that in many cases, queries written with IN and EXISTS are provably identical, and produce identical execution plans. In those cases, debating which is or isn't more efficient is an utterly moot point. The text is different, but the execution is the same.