SQL - Anti-join

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - 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.

Advertising

3 - Example

table1
(inner set)
table2
(outer set)
Column ID Column ID Column ID_2
A A
B B A
D C C
[email protected]>SELECT table1.id "ID_table1"
  2    FROM table1
  3   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.