Oracle Joins


The basic definition of join means creating a combination or a group of items, so an SQL join is a way to combine rows from two or more tables based on a common column between them now let’s see how oracle joins works

What are Oracle Joins

A query that combines two or more tables, columns, or views together based on the values of related columns in both tables is known as an Oracle join. For example, the primary key of the first table and the foreign keys of the second table are related columns for extracting relevant data from the database, and joins can be inner  join, outer join, left outer join, right outer join, and self-join, depending on the requirements, and all these joins are supported in the Oracle database.

Now let’s take an example and understand how oracle join works

Consider there are two tables

1) Orders_Details

Order_Id Customer_Id Order_Date
1089 3 01-12-2012
9802 45 02-12-2012
7845 55 03-12-2012


2) Customers_Details

Customer_Id Customer_Name Contact_Name Country
01 Rachel Jain Maria Anderas Germany
02 Monica Geller Moni Algeria
03 Kritika K Kurtz India


The query for joining the above tables

SELECT Orders_Details.Order_Id,Customers_Details.Customer_Name, Orders_Details.Order_Date
FROM Orders_Details
INNER JOIN Customers_Details ON Orders_Details.Customer_Id=Customers_Details.Customer_Id;


Output of the query:

Order_Id Customer_Name Order_Date
1089 Rachel Jain 01-12-2012
9802 Monica Geller 02-12-2012
7845 Kritika K 03-12-2012

Oracle Joins Types

Inner Join

The INNER JOIN keyword picks records in both tables that have the same value.


SELECT column_name(s)
FROM table1
ON table1.column_name = table2.column_name;



SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;


Outer Join

Full Outer join will return all the rows from both the tables


SELECT d.department_id,d.role, d.department_name FROM employees e departments d ON(e.department_id = d.department_id);

Even if there is no match in the DEPARTMENT’s field, this query returns all items in the EMPLOYEES table. Even if there is no match in the EMPLOYEES table, it fetches all entries in the DEPARTMENTS table.


There are two types of outer joins

  1. Left Outer Join:

This will return all the rows from the left table and the same rows from the right table.


SELECT e.employee_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d

ON(e.department_id = d.department_id);

Even if there is no match in the DEPARTMENTS table, this query returns all rows in the EMPLOYEES table, which is the left table.


  1. Right Outer Join:

This will return all the rows from the right table and the same rows from the left table.


SELECT e.department_id,  d.department_name,d.location_id FROM employees e RIGHT OUTER JOIN departments ds

ON(e.department_id = d.department_id);

Even if there is no match in the EMPLOYEES dataset, this query returns all entries from the DEPARTMENTS table, which is the table to the right.


Equi Join

Oracle Equi Joins retrieves the column values of many tables that match. The WHERE clause of the select statement contains a join condition or a comparison operator.

The Oracle Equijoin returns the intersect records of table1 and table2 as a result


SELECT columns [ , column ] FROM table1 , table2
where table1.column = table2.column;


SELECT e.employee_name, d.department_name
FROM employee e, department d
where e.employee _id = d.employee _id;

This Oracle Equijoin example will retrieve all items with the same employee _id value from the employee and department databases.



With the help of table aliases names, the table is used twice in the FROM clause in self join. To put it another way, the self attaches itself to a table. The Oracle Self Join joins rows in a table and returns them if the join condition is true.


SELECT e.employee_name || ‘works for’ || e.employee_name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id
ORDER BY e1.employee_name;


Cross Join

When there is no join condition between the two tables, a cross join is used. The Cartesian product of the two tables is returned by the cross join, which is a Cartesian product in which each row of one table combines with each row of the other table. If table 1 has 100 rows and table 2 has 10, the join result will have 1000 rows.


SELECT e.employee_name, d.department_name
FROM employee e
CROSS JOIN department d

This example of a cross JOIN returns all rows of the employee table plus all rows of the department table.



The antijoin returns contain rows from the LEFT table where the joined condition is true (as stated in the ON condition).


SELECT e.employee_name, d.department_name
FROM employee e
WHERE department_id NOT IN (SELECT department_id from department d
WHERE department_name = ‘sales’;

The rows from the employee table will be returned in this Oracle ANTI JOIN example if the joined condition is true.


Semi Join

The semi-join returns contain unique entries from the LEFT table when the EXISTS subquery matches (as specified in the ON condition).


SELECT e.employee _id, e.employee_name
FROM employee e
WHERE EXISTS (SELECT department_id FROM department
WHERE department_name = ‘sales’);

If the EXISTS parameter is true, the preceding Oracle SEMI JOIN example will retrieve entries from the employee table


  • Barry Allen

    A Full Stack Developer with 10+ years of experience in different domain including SAP, Blockchain, AI and Web Development.

    View all posts


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.