Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

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

  1. CREATE TABLE  “DEPARTMENTS”
  2.    (    “DEPARTMENT_ID” NUMBER(10,0) NOT NULL ENABLE,
  3.     “DEPARTMENT_NAME” VARCHAR2(50) NOT NULL ENABLE,
  4.      CONSTRAINT “DEPARTMENTS_PK” PRIMARY KEY (“DEPARTMENT_ID”) ENABLE
  5.    )
  6. /

semi-join1

Customer table

  1. CREATE TABLE  “CUSTOMER”
  2.    (    “CUSTOMER_ID” NUMBER,
  3.     “FIRST_NAME” VARCHAR2(4000),
  4.     “LAST_NAME” VARCHAR2(4000),
  5.     “DEPARTMENT_ID” NUMBER
  6.    )
  7. /

semi-join2

Execute this query

  1. SELECT   departments.department_id, departments.department_name
  2.         FROM     departments
  3.         WHERE    EXISTS
  4.                  (
  5.                  SELECT 1
  6.                  FROM   customer
  7.                  WHERE customer.department_id = departments.department_id
  8.                  )
  9.         ORDER BY departments.department_id;

Output

semi-join3

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.

Comments

comments