CodesJava

Easy learning with example program codes

ANTI JOIN in Oracle

ANTI JOIN
Anti-join is used to return one copy of those rows from a table where no matches are found in the values with the other mentioned table, and to serve this purpose, NOT EXISTS or NOT IN constructs are used instead of any JOIN keyword. The main advantage of this kind of Join query is that it makes the queries run faster and thus is a very powerful SQL construct.

Syntax:

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

Example :
Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        20
2	        Smiley	        19
3	        Happy	        21
4	        James	        22
5	        Bond	        25

Teachers Table:

TEACHER_ID	TEACHER_NAME	TEACHER_AGE
101	        James	        22
102	        Bond	        25
103	        Smith	        40

Query:

SELECT students.student_id, students.student_name  
FROM students
WHERE NOT EXISTS (  
SELECT 25  
FROM teachers 
WHERE teachers.teacher_age = students.student_age);  

Output:

STUDENT_ID	STUDENT_NAME
4	        James

Explanation:
The ‘students’ and the ‘teachers’ are the already existing tables. After the joining, the selected fields of the rows of the ‘students’ table satisfying the equality condition will be displayed as the result, but this equality condition is valid only for those rows in the ‘students’ table that does not have the value of student_age as 25.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status