In Oracle, a join is the most powerful operation for merging information from multiple tables based on a common field. There are various types of joins but an INNER JOIN is the common of them.
Syntax
SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;
Types Of Joins
![Types Of Joins]()
To understand each of the preceding joins clearly we are assuming the following "CUSTOMER" and "ORDERS" tables:
CREATE TABLE Customer
(
Cust_id Number(10) NOT NULL,
Cust_name varchar2(20),
Country varchar2(20),
Receipt_no Number(10),
Order_id Number(10) NOT NULL,
);
CREATE TABLE Orders
(
Order_id Number(10),
Item_ordered varchar2(20),
Order_date date
);
Table: CUSTOMER
![Customer table]()
Table: ORDERS
![Order table]()
First of all we will explain the "USING" clause and the "ON" clause.
1. Using Clause
To join a table using the USING Clause we write the following command.
Query
SELECT Cust_id, Cust_name, Country, item_Ordered, Order_date
FROM Customer C JOIN Orders O
USING (Order_id);
Execution of the query with result
![Execution of the Join query]()
2. On Clause
To join a table using an ON Clause we write the following command:
Query
SELECT Cust_id, Cust_name, Country, item_Ordered, Order_date
FROM Customer C JOIN Orders O
USING (C.Order_id = O.Order_id);
Execution of the query with result
![Join query with on clause]()
Equi Join
An Equi join is used to get the data from multiple tables where the names are common and the columns are specified. It includes the equal ("=") operator.
Example
SELECT Cust_id, Cust_name, item_Ordered, Order_date
FROM Customer C, Orders O
WHERE C.Order_id = O.Order_id;
Execution of the query with result
![Equi Join]()
1. Inner Join
An Inner Join retrieves the matching records, in other words it retrieves all the rows where there is at least one match in the tables.
Example
SELECT Cust_id, Cust_name, Country, item_ordered, Order_date
FROM Customer INNER JOIN Orders
USING (Order_id);
Execution of the query with result
![Inner Join Query]()
2. Outer Join
The records that don't match will be retrieved by the Outer join. It is of the following three types:
-
Left Outer Join
-
Right Outer Join
-
Full Outer Join
1. Left Outer Join
A Left outer join retrieves all records from the left hand side of the table with all the matched records. This query can be written in one of the following two ways.
Example
Method 1
SELECT Cust_id, Cust_name, Country, item_ordered, Order_date
FROM customer C, LEFT OUTER JOIN Orders O
ON (C. Order_id = O.Order_id)
Execution of the query with result
![Left Outer Join]()
Or:
Method 2
SELECT Cust_id, Cust_name, Country, item_ordered, Order_date
FROM customer C, Orders O
WHERE C.Order_id = O.Order_id(+);
Execution of the query with result
![Left Outer Join Query Result]()
2. Right Outer JoinA Right Outer Join retrieves the records from the right hand side columns.
Example
Method 1
SELECT Cust_id, Cust_name, Country, item_ordered, Order_date
FROM customer C, RIGHT OUTER JOIN Orders O
ON (C. Order_id = O.Order_id)
Execution of the query with result
![Right Outer Join]()
Or:
Method 2
SELECT Cust_id, Cust_name, Country, item_ordered, Order_date
FROM customer C, Orders O
WHERE C.Order_id(+)= O.Order_id;
Execution of the query with result
![Right Outer Join Query Result]()
3. Full Outer Join
To retrieve all the records, both matching and unmatched from all the tables then use the FULL OUTER JOIN.
Example
SELECT Cust_id, Cust_name, Country, item_ordered, Order_date
FROM customer C, FULL OUTER JOIN Orders OON (C. Order_id = O.Order_id)
Execution of the query with result
![Full Outer Join]()
Next Article:
Joins in Oracle: Part 2