Oracle Semi Join | Semi Join - oracle tutorial - sql tutorial
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
![oracle semi join](https://wikitechy.com/tutorials/oracle/img\oracle-images\semi-join.png)
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
![semi join customer table](https://wikitechy.com/tutorials/oracle/img\oracle-images\semi-join-customer-id.png)
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
Output
![join ouput](https://wikitechy.com/tutorials/oracle/img\oracle-images\semi-join-output.png)
Oracle ALL Joins
![Oracle ALL Joins](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-joins-all.png)