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

-- Is logically equivalent to...

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

1 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.