Sample Header Ad - 728x90

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

0 votes
1 answer
157 views
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.
Asked by J. Mini (1235 rep)
Nov 11, 2023, 10:42 PM
Last activity: Nov 13, 2023, 06:46 AM