w3resource logo

:

SQL Tutorial

SQL Cross Join

What is Cross Join in SQL?

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.

If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

Syntax:

SELECT * 
FROM table1 
CROSS JOIN table2;

Pictorial Presentation of Cross Join syntax

Sql cross join syntax

Example:

Here is an example of cross join in SQL between two tables.

Sample table: foods


Sample table: company


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;

or

SQL Code:

SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city 
FROM foods,company;

How cross joining happend into two tables

Sql cross join 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
.........
.........

More presentaion of the said output:

SQL Cross join

CROSS JOINS: Relational Databases

Key points to remember

Click on the following to get the slides presentation -

SQL JOINS, slide presentation

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.