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”
- 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
- CREATE TABLE “CUSTOMER”
- ( “CUSTOMER_ID” NUMBER,
- “FIRST_NAME” VARCHAR2(4000),
- “LAST_NAME” VARCHAR2(4000),
- “DEPARTMENT_ID” NUMBER
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;
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.