Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?

Question

In the past, there was a lot of paranoia around IN. In particular, I remember claims that it can be optimised differently to an equivalent WHERE EXISTS. Is this claim still true? Or is it now guaranteed that IN and WHERE EXISTS will always give the same execution plans in cases where the two queries are logically equivalent?

I am talking about queries of the following form

SELECT [...] FROM [FOO] WHERE [FOO_ID] IN (SELECT [FOO_ID] FROM [BAR])
-- Is logically equivalent to...
SELECT [...] FROM [FOO] WHERE EXISTS (SELECT 1 FROM [BAR] WHERE [BAR].[FOO_ID] = [FOO].[FOO_ID]) 

Not that I am not talking about NOT IN, NOT EXISTS, or using multiple IN clauses.

Answer 1

The claim is still true, but it was only ever a concern for long hard-coded IN expression lists. The issue is primarily around the extra compilation time, although weird query plans can also result, due to them being compiled into lots of OR clauses.

Your example is different: it uses an IN subquery, which is transformed at an early stage into a semi-join, which is similar to an EXISTS anyway. The compilation time should be pretty much the same, and you can see for yourself from this fiddle that the query plans are identical, whether or not the tables are indexed.

Yes, plans are not always identical, but that is usually the result of the fact you are getting a fresh compilation, where the optimizer cannot decide between two bad plans (you could equally get this by recompiling the same query). If a good access method is available on the subquery then expect the plans to usually be identical.