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