Using the EXISTS Function and the Nested Subquery in Oracle

A helpful tip to remember is to use the EXISTS function instead of the IN function in most circumstances. The EXISTS function in Oracle checks to find a single matching row to return the result in a subquery. Because the IN function retrieves and checks all rows, it is slower.

Oracle has also improved the optimizer so it often performs this optimization for you as well. Consider the following example, where the IN function leads to very poor performance. This query is faster only if the ITEMS table is extremely small:

 

 

 

 

 

 

 

 

 

 

 

In this query, the entire ITEMS table is retrieved. This query is faster when the condition PRODUCT_ID = 167 substantially limits the outside query:

 

 

TIP

Using the nested subquery with an EXISTS clause may make queries dramatically faster, depending on the data being retrieved from each part of the query. Oracle11g often makes this translation internally, saving you time and giving you performance gains!