Joins in Oracle: Part 1

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:

  1. Left Outer Join

  2. Right Outer Join

  3. 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 Join

A 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