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.

Pictorial Presentation of SQL Cross Join syntax

Sql cross join syntax

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.


FROM table1 
CROSS JOIN table2;


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,
FROM foods 
CROSS JOIN company;


SQL Code:

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

How cross joining happend into two tables

Sql cross join into two tables


--------------- ---------- --------------- ---------------
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.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.


Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

A table name as a variable -

For static queries, like the one in your question, table names and column names need to be static.

For dynamic queries, you should generate the full SQL dynamically, and use sp_executesql to execute it.

Static query:


Ref: https://bit.ly/3sFxxnk


We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook