What is Oracle semi join ?

• 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 "wikitechy_departments" and "wikitechy_customer"

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

click below button to copy the code. By - oracle tutorial - team

Copy Code

Wikitechy_Customer table
CREATE TABLE  "Wikitechy_CUSTOMER" 
   (	"CUSTOMER_ID" NUMBER, 
	"FIRST_NAME" VARCHAR2(4000), 
	"LAST_NAME" VARCHAR2(4000), 
	"DEPARTMENT_ID" NUMBER
   )
/

click below button to copy the code. By - oracle tutorial - team

Copy Code

Execute this query

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

click below button to copy the code. By - oracle tutorial - team

Copy Code

Output

Oracle ALL Joins

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning