About
An anti-join is a form of join with reverse logic. Instead of returning rows when there is a match (according to the join predicate) between the left and right side, an anti-join returns those rows from the left side of the predicate for which there is no match on the right. This behavior is exactly that of a NOT IN subquery with the right side of the anti-join predicate corresponding to the subquery.
Articles Related
Example
| table1 (inner set) | table2 (outer set) |
|
|---|---|---|
| Column ID | Column ID | Column ID_2 |
| A | A | |
| B | B | A |
| D | C | C |
select table1.id "ID_table1"
from table1
where table1.id NOT IN ( select ID from table2 );
ID_table1
----------
D
The value D is the only value that is in the table2 but not in table1.