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

 

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

Table: ORDERS

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

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

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



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

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

  

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

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

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

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



Next Article: Joins in Oracle: Part 2