IN and EXISTS Operator

What is the Difference Between IN and EXISTS Operator and how they can be used for different purposes?

Oracle IN operator and EXISTS operator work for the same purpose i.e. they both check for record correlation between the main query and the sub query. We can compare the working of IN and EXISTS operator. Let's see some differences below :

IN Operator	Exists Operator
The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is 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 result for checking. This process of  inner query execution repeats as many no. of times    as  there are outer query rows. That is, if there are  ten rows that can result from outer query, the inner  query is executed that many nos. of times.
In operator test for the particular value in the sub query.	Exist operator is a Boolean operator.so it works more efficient and faster than IN operator as it will be faster to process Boolean value rather than processing values itself.
In operator scan all the values inside the IN block.	EXISTS quit after 1st occurrence.
In case of a big number of list from the select list IN operator should not be used.	The exists shall be used with the proper condition this will definitely make the query optimal query.
'IN' can be used on sub-queries as well as with values.	Whereas 'EXISTS' can only be used on sub-queries.
IN is used as multiple OR operators. IN picks the list of matching values.	Exists is used to check whether the sub-query returns any rows. EXISTS returns the Boolean values like true or false. Exists is faster than in.

Now Let’s understand this practically.

Consider below two tables-

 SQL> SELECT * From TT1;  
    COL1  
 ----------  
      1  
      2  
      3  
      4  

 SQL> SELECT * From TT2;  
    COL2    COL3  
 ---------- ----------  
      1     4  
      2     6  
      3     7  
      4     8  

Get the values from Table1(TT1) which are present in Col3 of Table2(TT2).

/*Using IN Operator*/

SQL> SELECT * From TT1 Where Col1 IN(SELECt Col3 From TT2);  
     COL1  
  ----------  
      4  

/*Using EXISTS Operator*/

SQL> SELECT * From TT1 Where EXISTS(SELECT 'X' From TT2 Where TT1.Col1=TT2.Col3);  
     COL1  
  ----------  
      4  

Note- when using EXISTS always use the where clause in the subquery to join the tables. Not doing so will result in fetching all the records from the main table. for example, if we consider the query below will fetch all the records from Table1(TT1) which will be same as “Select * From TT1”

SQL> SELECT * From TT1  
     Where EXISTS(SELECT Col3 From TT2);  
     COL1  
 ----------  
      1  
      2  
      3  
      4  

EXISTS works faster than IN. you can check the performance plans of the above query for more info.

Summary:

• If the majority of the filtering criteria is in the Subquery, use IN (IN for big outer query and small inner query).
• If the majority of the filtering criteria is in the main query, use EXISTS (EXISTS for small outer query and big inner query).

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.