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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×