Home >> Oracle >> Anti Join in Oracle

Anti Join in Oracle

Anti join between two tables returns rows from the first table where no matches record are found in the another table.

Anti join is used to make the queries run faster. 

Anti join using the NOT EXISTS or NOT IN constructs.

Syntax :

SELECT column_name(s) FROM table_1 WHERE NOT EXISTS (SELECT values FROM table_2 WHERE table_2.column = table_1.column);  

Example

employee table
id    name       salary       city
1     Ram        200000       Delhi
2     Shyam      10000        Ghaziabad
3     Ghanshyam  30000        Allahabad
4     Ramesh     200000       Noida

employee_details table
id      address                         city
10     New Delhi-110096        Delhi
11     Ghaziabad-23355         Ghaziabad
12     Allahabad 230001        Allahabad
13     Lucknow-234000          Lucknow

Query

SELECT `employee`.`id`,`employee`.`name` FROM `employee` WHERE 
      NOT EXISTS (SELECT 'Lucknow' FROM `employee_details` WHERE `employee_details`.`city`=`employee`.`city`)

Output
id        name         
4         Ramesh

Post Your Comment

Next Questions
Semi Join
Create database
Drop database
Create table
Drop table
Truncate table
Alter Table
Create table AS
Global Temporary tables
SELECT Query
INSERT Query
INSERT ALL Query
UPDATE Query
DELETE Query
WHERE clause
AND Clause
OR Clause
LIKE clause
IN clause
ORDER BY Clause
GROUP BY Clause
HAVING Clause
BETWEEN
EXISTS
NOT EXITS

Copyright ©2022 coderraj.com. All Rights Reserved.