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
   )
/
 

Customer table

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;

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.

Next TopicOracle Procedure

← prev next →