76

Oracle Semi Join

Semi-join is introduced in Oracle 8.0. It provides an efficient method of performing a WHERE EXISTS sub-query.

A semi-join returns one copy of each row in first table for which at least one match is found.

Semi-joins are written using the EXISTS construct.

Oracle Semi Join Example

Let’s take two tables “departments” and “customer”

Departments table

Customer table

Execute this query

Output

Difference between anti-join and semi-join

While a semi-join returns one copy of each row in the first table for which at least one match is found, an anti-join returns one copy of each row in the first table for which no match is found.