IN or EXISTS

[Allan Paul]

Which clause is better to use in subqueries of SQL, IN or EXISTS clause? Do they have the same output?

[RyanJames]

When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query .When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query . So determine which gives better performance in rule-based optimization one of the steps could be to consider or analyze the rows the inner query will return in comparison to the outer query. Decision made based on this will increase the performance.

[debasisdas]

It has been my experience that as a general rule of thumb, Oracle processes the IN operator faster than the EXISTS operator.

[debasisdas]

To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.

[charnu]

The EXISTS construct is a TSQL function that returns a Boolean value based upon the subquery inside of the parentheses. If the subquery returns any rows, the construct returns true else it will return false. To my experience I have always found use of Exists construct faster compared to IN.

[debasisdas]

Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset (nested loops, merge, whatever). If the table in the subquery is
small, and the table in the main query is large, then IN usually makes
sense.

Using "EXISTS", the optimizer generally runs the main query first, then
applies that dataset to the subquery. If the table in the subquery is
large, and the table in the main query is small, then EXISTS usually makes
sense.