cross join | Oracle Cross Join - oracle tutorial - sql tutorial
What is Cross Join in Oracle?
- The CROSS JOIN clause produces the cross-product of two tables.
- A cross join or Cartesian product is formed when every row from one table is joined to all rows in another.
- Suppose, the source and target tables have four and three rows, respectively, a cross join between them results in (4 × 3 = 12) rows being returned provided by there is no WHERE clause have been applied with the cross join statement.
- The CROSS JOIN specifies that all rows from first table join with all of the rows of second table. If there are "x" rows in table1 and "y" rows in table2 then the cross join result set have x*y 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.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql
Syntax:
SELECT table1.column, table2.column
FROM table1
CROSS JOIN table2 click below button to copy the code. By - oracle tutorial - team
Example:
- Here is an example of cross join in SQL between two tables.
Sample table: foods
| ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
|---|---|---|---|
| 1 | Chex Mix | Pcs | 16 |
| 6 | Cheez-It | Pcs | 15 |
| 2 | BN Biscuit | Pcs | 15 |
| 3 | Mighty Munch | Pcs | 17 |
| 4 | Pot Rice | Pcs | 15 |
| 5 | Jaffa Cakes | Pcs | 18 |
| 7 | Salt n Shake | Pcs |
Sample table: company
| COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
|---|---|---|
| 18 | Order All | Boston |
| 15 | Jack Hill Ltd | London |
| 16 | Akas Foods | Delhi |
| 17 | Foodies | London |
| 19 | sip-n-Bite | New York |
- To get item name and item unit columns from foods table and company name, company city columns from company table, after a CROSS JOINING with these mentioned tables, the following SQL statement can be used:
SQL Code:
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city FROM foods CROSS JOIN company;
click below button to copy the code. By - oracle tutorial - team
or
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city FROM foods,company;
click below button to copy the code. By - oracle tutorial - team
How cross joining happend into two tables
Output:
| ITEM_NAME | ITEM_UNIT | COMPANY_NAME | COMPANY_CITY |
|---|---|---|---|
| Chex Mix | Pcs | Order All | Boston |
| Cheez-It | Pcs | Order All | Boston |
| BN Biscuit | Pcs | Order All | Boston |
| Mighty Munch | Pcs | Order All | Boston |
| Pot Rice | Pcs | Order All | Boston |
| Jaffa Cakes | Pcs | Order All | Boston |
| Salt n Shake | Pcs | Order All | Boston |
| Chex Mix | Pcs | Jack Hill Ltd | London |
| Cheez-It | Pcs | Jack Hill Ltd | London |
| BN Biscuit | Pcs | Jack Hill Ltd | London |
| Mighty Munch | Pcs | Jack Hill Ltd | London |
| Pot Rice | Pcs | Jack Hill Ltd | London |
| Jaffa Cakes | Pcs | Jack Hill Ltd | London |
| Salt n Shake | Pcs | Jack Hill Ltd | London |
| Chex Mix | Pcs | Akas Foods | Delhi |
| Cheez-It | Pcs | Akas Foods | Delhi |
| BN Biscuit | Pcs | Akas Foods | Delhi |
| Mighty Munch | Pcs | Akas Foods | Delhi |
| Pot Rice | Pcs | Akas Foods | Delhi |
| Jaffa Cakes | Pcs | Akas Foods | Delhi |
| Salt n Shake | Pcs | Akas Foods | Delhi |
| Chex Mix | Pcs | Foodies | London |
