Joins in Oracle: Part 2

2. Non-Equi Join

A Non-Equi join is based on a condition using an operator other than equal to "=".

Example

SELECT Cust_id, Cust_name, Country, Item_ordered, Order_date
FROM
Customer C, Oredrs O
WHERE
C. Order_id > O.Order_id;

 
Execution of the query with result:

Non Equi Join in Oracle
 
3. Self-join

When a table is joined to itself only then that condition is called a self-join.

Example

SELECT C1.Cust_id, C2.Cust_name, C1.Country, C2.Order_id
FROM
Customer C1, Customer C2
WHERE
C. Cust_id > O.Order_id;

 
Execution of the query with result:

Self Join in Oracle
 
4. Natural Join
 
A natural join is just like an equi-join since it compares the common columns of both tables.
 
Example

SELECT Cust_id, Cust_name, Country, Item_ordered, Order_date
FROM
Customer, NATURAL JOIN Orders;


 
Execution of the query with result:

Natural Join in Oracle
  
5. Cross Join

This join is a little bit different from the other joins since it generates the Cartesian product of two tables as in the following:

Cross Join in Oracle

Syntax

SELECT *
FROM
table_name1
CROSS
JOIN table_name2;


Example

SELECT Cust_id, Cust_name, Country, Item_ordered, Order_date
FROM
Customer,
CROSS
JOIN Orders;

 
Execution of the query with result:

Cross Join script  in Oracle

Previous article: Joins in Oracle: Part 1