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
 Cross Join

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

 Cross Join

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
 Cross Join

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to cross join | Oracle Cross Join