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

CREATE TABLE "DEPARTMENTS"
( "DEPARTMENT_ID" NUMBER(10,0) NOT NULL ENABLE,
"DEPARTMENT_NAME" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "DEPARTMENTS_PK" PRIMARY KEY ("DEPARTMENT_ID") ENABLE
)
/

Oracle Semi Join

Customer table

CREATE TABLE "CUSTOMER"
( "CUSTOMER_ID" NUMBER,
"FIRST_NAME" VARCHAR2(4000),
"LAST_NAME" VARCHAR2(4000),
"DEPARTMENT_ID" NUMBER
)
/

Oracle Semi Join 2

Execute this query

SELECT departments.department_id, departments.department_name
FROM departments
WHERE EXISTS
(
SELECT 1
FROM customer
WHERE customer.department_id = departments.department_id
)
ORDER BY departments.department_id;

Output

Oracle Semi Join 3

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.

DATABASE

SQL

VIDEOS

Orcale SQL Tutorial

Semi Join

Oracle Semi Join

Oracle Semi Join Example

Difference between anti-join and semi-join