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
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 EXISTS, or using multiple