anti Join SQL | Oracle Anti Join - oracle tutorial - sql tutorial



What is Oracle Anti-join ?

  • An anti-join, also called an excluding-join or left-outer-join, comes from requesting data from a table where some value is not in another table.
 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
   )
click below button to copy the code. By - oracle tutorial - team
 Anti Join

Customer table

CREATE TABLE  "CUSTOMER"  
   (	"CUSTOMER_ID" NUMBER, 
	"FIRST_NAME" VARCHAR2(4000), 
	"LAST_NAME" VARCHAR2(4000), 
	"DEPARTMENT_ID" NUMBER
   )
click below button to copy the code. By - oracle tutorial - team
 Anti Join
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

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;


click below button to copy the code. By - oracle tutorial - team

Output

 Anti Join

Oracle ALL Joins

Oracle ALL Joins

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to oracle join | Oracle Anti Join