Table of Contents
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
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
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;
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
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
- 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.
- 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.
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;
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.
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