Oracle: Anti Join

Anti Join in Oracle is a special kind of join that makes queries run faster. The Join helps to return those values that are only present in Table1. Any values of Table2 that are present in Table1, get filtered out when we apply Anti Join between both the tables. Anti Join is performed by using the NOT IN Clause.

Syntax

Select Column from Table1 
where Column 
NOT IN ( Select Column from Table2);

ANTI JOIN

Example

We have two tables Stu and marks table. Refer to the values in both the table below:

Stu table is as follows:
ANTI JOIN

marks Table is as follows:
ANTI JOIN

Now if we want only those values in Stu table and not in the marks table, we can use anti Join as follows:

--Anti Join
select * from Stu
where 
Roll_no NOT IN (select Roll_no from marks );

Output

ANTI JOIN

NOTE: Only those values are displayed as an output that was present in Stu and not in marks.