17 - Difference between anti-join and semi-join

Difference Between Anti-Join and Semi-Join

While a semi-join returns one copy of each row in the first table for which at least one match is found, an anti-join returns one copy of each row in the first table for which no match is found.

Oracle Semi Join

·        Semi-join is introduced in Oracle 8.0. It provides an efficient method of performing a WHERE EXISTS sub-query.

·        A semi-join returns one copy of each row in first table for which at least one match is found.

·        Semi-joins are written using the EXISTS clause

DEPT Table

CREATE TABLE ”DEPT"   

   (    

"D_ID" NUMBER(10,0) NOT NULL PRIMARY KEY,   

 "D_NAME" VARCHAR2(50) NOT NULL

   )                                          

D_ID	D_NAME
1	A
2	B
3	C
4	d

 

EMP Table

CREATE TABLE  "EMP"   

   (   

 "E_ID" NUMBER,   

 "F_NAME" VARCHAR2(4000),   

"L_NAME" VARCHAR2(4000),   

 "D_ID" NUMBER  

   )  

E_ID	F_NAME	L_NAME	D_ID
1	Ram	Kumar	-
2	Raj	Verma	-
3	Sam	Sharma	-
4	John	Yadav	-
10	Amit	Sharma	1

 

Execute this query

SELECT   d.d_id, d.d_name  

        FROM     dept AS d

        WHERE    EXISTS  

                 (  

                 SELECT 1  

                 FROM   emp AS e

                 WHERE e.d_id = d.d_id  

                 )  

        ORDER BY d.d_id;  

Result

D_ID	D_NAME
1	A

 

Result only from Left Table when subquery result minimum one row i.e. exists will be true.

 

Oracle Anti Join

·        Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries.

·        Anti-join between two tables returns rows from the first table where no matches are found in the second table. It is opposite of a semi-join. An anti-join returns one copy of each row in the first table for which no match is found.

·        Anti-joins are written using the NOT EXISTS or NOT IN constructs.

 

Execute this query

SELECT   d.d_id, d.d_name  

        FROM     dept  

        WHERE    NOT EXISTS  

                 (  

                 SELECT 1  

                 FROM   emp as e

                 WHERE e.d_id = d.d_id  

                 )  

        ORDER BY d.d_id ;  

 

 

Result

D_ID	D_NAME
2	B
3	C
4	D

 

 

Result from left table which is not match in right table