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:


 
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:


 
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:


  
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:



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:

Previous article: Joins in Oracle: Part 1