CROSS JOIN(Cartesian Product)

---

Oracle - Cross Join

The CROSS JOIN specifies that all rows from first table join with all of the rows of second table. If there are "a" rows in table1 and "b" rows in table2 then the cross join result set have a*b rows. It normally happens when no matching join columns are specified.

In simple words you can say that if two tables in a join query have no join condition, then the Oracle returns their Cartesian product.

Syntax

SELECT *   
FROM table1   
CROSS JOIN table2;  

OR

SELECT * FROM table1, table2  

Both the above syntax are same and used for Cartesian product. They provide similar result after execution.

Oracle Cross Join Example

Let's take two tables "customer" and "supplier".

Customer table detail

CUSTOMER_ID	FIRST_NAME	LAST_NAME
1	Niraksh	sarkar
2	Niroop	Pritala
3	Sweejya	Mehtha
4	Deepu	Nehra

Supplier table detail

SUPPLIER_ID	FIRST_NAME	LAST_NAME
1	Ajay	Bhalla
2	Nynika	Kapoor
3	Farah	Khan

Execute this query :

SELECT * FROM Customer,Supplier;

OR

SELECT * 
FROM Customer 
CROSS JOIN Supplier;

OUTPUT :

CUSTOMER_ID	FIRST_NAME	LAST_NAME	SUPPLIER_ID	FIRST_NAME	LAST_NAME
1	Niraksh	sarkar	1	Ajay	Bhalla
1	Niraksh	sarkar	2	Nynika	Kapoor
1	Niraksh	sarkar	3	Farah	Khan
2	Niroop	Pritala	1	Ajay	Bhalla
2	Niroop	Pritala	2	Nynika	Kapoor
2	Niroop	Pritala	3	Farah	Khan
3	Sweejya	Mehtha	1	Ajay	Bhalla
3	Sweejya	Mehtha	2	Nynika	Kapoor
3	Sweejya	Mehtha	3	Farah	Khan
4	Deepu	Nehra	1	Ajay	Bhalla
4	Deepu	Nehra	2	Nynika	Kapoor
4	Deepu	Nehra	3	Farah	Khan