SQL - Anti-join

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.

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.


Powered by ComboStrap