ORACLE | ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
| ||||||||
|
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.
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.
Let's take two tables "customer" and "supplier".
CUSTOMER_ID | FIRST_NAME | LAST_NAME |
---|---|---|
1 | Niraksh | sarkar |
2 | Niroop | Pritala |
3 | Sweejya | Mehtha |
4 | Deepu | Nehra |
SUPPLIER_ID | FIRST_NAME | LAST_NAME |
---|---|---|
1 | Ajay | Bhalla |
2 | Nynika | Kapoor |
3 | Farah | Khan |
SELECT * FROM Customer,Supplier;
OR
SELECT *
FROM Customer
CROSS JOIN Supplier;
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 |