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.
SELECT columns FROM table_1 WHERE NOT EXISTS ( SELECT values FROM table_2 WHERE table_2.column = table_1.column);
STUDENT_ID STUDENT_NAME STUDENT_AGE 1 Joy 20 2 Smiley 19 3 Happy 21 4 James 22 5 Bond 25
TEACHER_ID TEACHER_NAME TEACHER_AGE 101 James 22 102 Bond 25 103 Smith 40
SELECT students.student_id, students.student_name FROM students WHERE NOT EXISTS ( SELECT 25 FROM teachers WHERE teachers.teacher_age = students.student_age);
STUDENT_ID STUDENT_NAME 4 James
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.