Consider using EXISTS instead of IN

At one time, the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subquery returned any rows, then when evaluating WHERE EXISTS (subquery), the database engine could quit searching as soon as it had found just one row, whereas WHERE IN (subquery) would always collect all the results from the sub-query, before further processing.However, the query optimizer now treats EXISTS and IN the same way, whenever it can, so you’re unlikely to see any significant performance differences.

For the NOT IN (subquery) or NOT EXISTS (subquery) variants, there can never be any performance difference regardless (the entire result set must be read in either case). However, you need to be cautious when using the NOT IN operator if the subquery’s source data contains NULL values. If so, you should always consider using a NOT EXISTS operator instead of NOT IN, or recast the statement as a left outer join.

For more information, and examples, see the following Product Learning article: Consider using [NOT] EXISTS instead of [NOT] IN (subquery)

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard