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