How to use IN and EXISTS in SQL effectively ?

Developers have this doubt in mind often on HOW and WHEN to use either IN or EXISTS.  Here is the solution.

Consider two tables - Shipment & Shipment Refnum

Shipment  ( Parent Table )
Shipment_gid    Varchar2
Domain_name   Varchar2


Shipment_Refnum ( Child Table )
Shipment_gid  Varchar2
Shipment_refnum_qual_gid   Varchar2
Shipemnt_refnum_value        Varchar2
Domain_name  Varchar2 


HOW to USE "IN"

When the INNER QUERY passes the direct output value to OUTER QUERY; we can use "IN" as below. 

SELECT *       /* outer query */
  FROM SHIPMENT S
 WHERE S.SHIPMENT_GID  IN
                                         ( SELECT    SHIPMENT_GID     /*  inner query */
                                                 FROM    SHIPMENT_REFNUM SR
                                             WHERE    SR.SHIPMENT_REFNUM_QUAL_GID = 'RQG'
                                                AND  SR.SHIPMENT_REFNUM_VALUE = 'SRV' )
AND S.DOMAIN_NAME = 'DUMMY';



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.


HOW to USE "EXISTS"

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 no.of times.

SELECT  *  FROM SHIPMENT S
                                               WHERE EXISTS
                                                                               (  SELECT  1  FROM SHIPMENT_REFNUM SR
                                                                                        WHERE SR.SHIPMENT_REFNUM_QUAL_GID = 'RQG'
                                                                                                AND  SR.SHIPMENT_REFNUM_VALUE = 'SRV'
                                                                                                AND SR.SHIPMENT_GID = S.SHIPMENT_GID )
AND S.DOMAIN_NAME = 'DUMMY';   

                     

WHEN to USE "IN"  &  "EXISTS"

The recommendation at that time were:

• If the majority of the filtering criteria is in the INNER query, use IN.
• If the majority of the filtering criteria is in the outer query, use EXISTS.

When the INNER query has the feasibility of returning very less number of records, IN is better.

When the INNER query has the feasibility of returning more number of records, EXISTS is better. 


In other words,

• IN for big OUTER query and small INNER query.
• EXISTS for small OUTER query and big INNER query.