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 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
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
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
Oracle ALL Joins
