ANTI JOIN

---

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.

Example :

Let's take two tables "departments" and "customer"

Departments table :

DEPARTMENT_ID	DEPARTMENT_NAME
1	A
2	B
3	C
4	D

Customer table :

CUSTOMER_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID
1	Niraksh	sarkar	-
2	Niroop	Paritala	-
3	Sweejya	Mehtha	-
4	Deepu	Nehra	1

Execute this query :

SELECT   departments.department_id, departments.department_name  
        FROM     departments  
        WHERE    NOT EXISTS  
                 (  
                 SELECT 1  
                 FROM   customer  
                 WHERE customer.department_id = departments.department_id  
                 )  
        ORDER BY departments.department_id; 

Output :

DEPARTMENT_ID	DEPARTMENT_NAME
2	B
3	C
4	D