SQL - Anti-join

Data System Architecture

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.





Discover More
Card Puncher Data Processing
Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Joins are data operation / method / algorithm in order to match data from more than one table. They all require sorting and then matching aspects of a dataset. There are only three join mechanisms used...
Oracle Database Star Transformation
Oracle Database - Star Transformation

This article talk the application of the star query in Oracle which involve a star schema. Star transformation executes the query in two phases: retrieves the necessary rows from the fact table (row...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...



Share this page:
Follow us:
Task Runner