Introduction

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

2) Customers_Details

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:

Oracle Joins Types

Inner Join

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

INNER JOIN Syntax

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

Example

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

Example

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.

Example:

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.

2. Right Outer Join:

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

Example:

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

Syntax

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

Example

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.

Self-Join

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.

Example

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.

Example

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.

Anti-Join

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

Example

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).

Example

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