Use EXISTS rather than IN
Oracle IN operator and EXISTS operator work for the same purpose and they check for the record correlation between the main query and the Sub query.
The wrong use of the IN and EXISTS operators is very common “SQL performance killer”. Although, in many cases, using either one or the other will give the same query result, they are processed very differently and knowing this difference is the key to avoid killing query performance.
|IN Operator||EXISTS Operator|
|Clause or a condition that helps to minimize the use of multiple OR conditions.||Clause or a condition that is used to combine the queries and creating Sub query.|
|IN operator test for the particular value in the sub query.||EXIST operator is a Boolean operator. It works more efficient and faster than IN operator.|
|IN operator scan all the values inside the IN block.||EXIST quit after 1st occurrence.|
|Helps to compare null.||Cannot be used to compare null.|
|Compares values between parent query and sub-query.||Cannot compare values between parent query and sub-query.|
|The Inner query is executed first and the list of values obtained as its results by the outer query. The inner query in executed for only once.||The first row from the outer query is selected. Then the inner query is executed and the outer query output uses this results for checking. This process of inner query executions repeats as many number of time as there are outer query rows.|
|Can have multiple direct values instead of sub query.||Cannot compare direct values it should have a sub query with SELECT.|
1. IN Operator typically processed as
The subquery is executed and result is distinct
Then joined to the external table
1. EXISTS Operator typically processed as
- For each row of the external table (Full Table Scan) the Sub query will be executed once.
when using IN is better than EXISTS, and vice versa?
If the majority of the filtering criteria is in the Sub query or in other words if the Sub query is fast and Main query is relatively slow and has many rows > use IN operator.
If the majority of the filtering criteria is in Main query or in other words if the Sub query is slow and Main query is relatively fast and small > use EXISTS operator
If both the Sub query and the Main query are small, IN and EXISTS will have the same performance.
If both the Sub query and the Main query are big, either will work, the performance will depend basically on the existing indexes.