Anti Join

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

  1. CREATE TABLE  "DEPARTMENTS"   
  2.    (    "DEPARTMENT_ID" NUMBER(10,0) NOT NULL ENABLE,   
  3.     "DEPARTMENT_NAME" VARCHAR2(50) NOT NULL ENABLE,   
  4.      CONSTRAINT "DEPARTMENTS_PK" PRIMARY KEY ("DEPARTMENT_ID") ENABLE  
  5.    )  
  6. /  

Oracle Anti Join

Customer table

  1. CREATE TABLE  "CUSTOMER"   
  2.    (    "CUSTOMER_ID" NUMBER,   
  3.     "FIRST_NAME" VARCHAR2(4000),   
  4.     "LAST_NAME" VARCHAR2(4000),   
  5.     "DEPARTMENT_ID" NUMBER  
  6.    )  
  7. /  

  1. Oracle Anti Join 2

    Execute this query

  2. SELECT   departments.department_id, departments.department_name  
  3.         FROM     departments  
  4.         WHERE    NOT EXISTS  
  5.                  (  
  6.                  SELECT 1  
  7.                  FROM   customer  
  8.                  WHERE customer.department_id = departments.department_id  
  9.                  )  
  10.         ORDER BY departments.department_id;  

 

Output

Oracle Anti Join 3