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
Last activity: Nov 13, 2023, 06:46 AM