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

 CREATE TABLE  "DEPARTMENTS"   

   (    "DEPARTMENT_ID" NUMBER(10,0) NOT NULL ENABLE,   

    "DEPARTMENT_NAME" VARCHAR2(50) NOT NULL ENABLE,   

     CONSTRAINT "DEPARTMENTS_PK" PRIMARY KEY ("DEPARTMENT_ID") ENABLE  

   )  

/ 

Customer table

CREATE TABLE  "CUSTOMER"   

   (    "CUSTOMER_ID" NUMBER,   

    "FIRST_NAME" VARCHAR2(4000),   

    "LAST_NAME" VARCHAR2(4000),   

    "DEPARTMENT_ID" NUMBER  

   )  

/

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