The Geek Diary

IN and EXISTS SQL Operator

by

EXISTS Operator

Use EXISTS to test if a subquery returns results or not. Typically, the subquery references another table. The following query returns employees who also happen to be customers:

SELECT e.id, e.name
FROM employees e
WHERE EXISTS (SELECT *
              FROM customers c
              WHERE c.email = e.email);

IN Operator

Use IN to test whether a value falls within a list of values. The following query returns values for a few employees:

SELECT *
FROM employees
WHERE e.id IN (10001, 10032, 10057);

The following query returns employees who have not taken a vacation day:

SELECT e.id
FROM employees e
WHERE e.id NOT IN (SELECT v.emp_id
                   FROM vacations v);

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. Exists is used to check whether the sub-query returns any rows.
IN picks the list of matching values. EXISTS returns the Boolean values like true or false. Exists is faster than in.

1. 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

2. 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).

You May Also Like