Oracle Semi Join

Oracle Semi Join | In this post, we will learn what is semi join in Oracle database and also what is the difference between the semi joins and anti joins in Oracle database.

What is Semi Join in Oracle?

Oracle semi join are those which return the rows that match an EXISTS subquery without replicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Semi-join can be achieved using the EXISTS or IN operator. Semi Join in Oracle allows the conversion of the EXISTS subquery into a Join. It returns only a single row from the driving table even if multiple matching rows exist in the Join table.

Syntax of Oracle Semi Join

SELECT COLUMNS
FROM   table_1
WHERE  EXISTS (
   SELECT values
   FROM   table_2
   WHERE  table_2.COLUMN = table_1.COLUMN
);

Oracle Semi Join Example

Let’s create sample tables “Departments” and “Employees” to demonstrate the Oracle semi joins.

Query to create table Departments. The departments’ table contains the primary key constraint in the “department_id” column. Learn more about constraints in the Oracle database.

CREATE TABLE departments
(
   department_id   NUMBER(10, 0),
   department_name VARCHAR2(50),
   CONSTRAINT departments_pk PRIMARY KEY (department_id)
); 

To Insert the data into the Department’s Table execute the insert query. And after inserting execute the select query to check the data in the table:

INSERT INTO departments VALUES(10, 'Information Technology');
INSERT INTO departments VALUES(11, 'Finance');
INSERT INTO departments VALUES(12, 'HR'); 
SELECT * FROM departments; 

Output:-

DEPARTMENT_ID  DEPARTMENT_NAME     
----------     -----------------
  10           Information Technology   
  11           Finance   
  12           HR

Let’s create another table Employees. This table contains a foreign key constraint at “department_id” which is mapped with “department_id” primary key constraint of the departments’ table.

CREATE TABLE employees
(
     employee_id   NUMBER(10, 0),
     employee_name VARCHAR2(50),
     department_id NUMBER(10, 0),
     PRIMARY KEY (employee_id),
     FOREIGN KEY (department_id) REFERENCES departments(department_id)
); 

To Insert the data into the Employees Table execute the insert query. And then execute the select query to check the data in the table.

INSERT INTO employees VALUES(1, 'Abram', 10);
INSERT INTO employees VALUES(2, 'Simon', 11);
INSERT INTO employees VALUES(3, 'Jack', 10);
INSERT INTO employees VALUES(4, 'Crusie', 10);
INSERT INTO employees VALUES(5, 'Shon', 10); 
SELECT * FROM   employees; 

Output:-

EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID
----------  ------------- -------------
     1        Abram           10 
     2        Simon           11  
     3        Jack            10
     4        Cruise          10    
     5        Shon            10
      

Let’s execute the semi-join query for the two tables department and employees:-

SELECT departments.department_id, departments.department_name
FROM   departments
WHERE  EXISTS (
   SELECT * FROM   employees
   WHERE  employees.department_id = departments.department_id
)
ORDER  BY departments.department_id; 

It gives the following output:-

DEPARTMENT_ID  DEPARTMENT_NAME
-------------- ---------------
      10       Information Technology
      11       Finance

The above output shows that no department appears more than once. Oracle stops the processing department as soon as the first employee in that department is found.

The above query also can be written as follows using the alias names. Here we have used the “d” alias name for the “departments” table and the “e” alias for the “employees” table.:-

SELECT d.department_id, d.department_name
FROM   departments d
WHERE  EXISTS (
   SELECT * FROM   employees e
   WHERE  e.department_id = d.department_id
)
ORDER  BY d.department_id; 

Difference Between Semi Joins and Anti Joins in Oracle

Let’s look at what is the difference between the semi joins and anti joins in Oracle?

Semi Joins return the rows which match an EXISTS subquery without replicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Anti Joins are those exactly opposite to the semi-joins. Anti joins use the NOT EXISTS or NOT in constructs. Anti join between two tables returns rows from the first table where no matches are found in the second table.

Let’s just have an example of an anti join in oracle as we have some tables defined above. Execute the below query:-

SELECT d.department_id, d.department_name
FROM   departments d
WHERE  NOT EXISTS (
   SELECT * FROM   employees e
   WHERE  e.department_id = d.department_id
)
ORDER  BY d.department_id; 

Let’s look at the output:-

DEPARTMENT_ID  DEPARTMENT_NAME 
-------------- ----------------
 12            HR  

From the above input we can say that, as no employees have a record of this department, it returns to the department which has no employees in that department.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search