SQL Cross Join

Last update on July 10 2024 10:10:06 (UTC/GMT +8 hours)

Introduction:

Cross JOINs in SQL can be a powerful tool when used correctly. This guide will help you understand what a CROSS JOIN is, how to use it, and when it might be useful.

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

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;

Alternative Syntax:

SELECT * 
FROM table1, table2;

When to Use a CROSS JOIN

To generate combinations of all rows between two tables for analysis.

When you need to perform operations on every combination of records.

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, -- Selecting the item name from the 'foods' table.
       foods.item_unit, -- Selecting the item unit from the 'foods' table.
       company.company_name, -- Selecting the company name from 
                             -- the 'company' table.
       company.company_city -- Selecting the company city from 
	                        -- the 'company' table.
FROM foods -- Specifying the first table to select data from, 
           -- which is 'foods'.
CROSS JOIN company; -- Performing a cross join with the 'company' table.

Explanation:

The SQL code retrieves specific columns from two tables, 'foods' and 'company', and combines them into a single result set using a cross join.

The query selects the 'item_name' and 'item_unit' columns from the 'foods' table and the 'company_name' and 'company_city' columns from the 'company' table.

It performs a cross join between the 'foods' and 'company' tables, resulting in a Cartesian product where every row from the 'foods' table is combined with every row from the 'company' table.

SQL Code:


SELECT foods.item_name, -- Selecting the item name from the 'foods' table.
       foods.item_unit, -- Selecting the item unit from the 'foods' table.
       company.company_name, -- Selecting the company name from 
	                         -- the 'company' table.
       company.company_city -- Selecting the company city from 
	                        -- the 'company' table.
FROM foods, company; -- Specifying the tables to select data from, 'foods' 
                     -- and 'company', using comma syntax.

Explanation:

The SQL code retrieves specific columns from two tables, 'foods' and 'company', without specifying a join condition, using comma syntax to indicate a cross join.

The query selects the 'item_name' and 'item_unit' columns from the 'foods' table and the 'company_name' and 'company_city' columns from the 'company' table.

It performs a cross join between the 'foods' and 'company' tables, resulting in a Cartesian product where every row from the 'foods' table is combined with every row from the 'company' table.

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

Performance Considerations

CROSS JOINs can produce very large result sets. Be cautious with large tables to avoid performance issues.

Prefer using specific join conditions to limit the result set when possible.

More presentaion of the said output:

Comparison with Other Joins

INNER JOIN: Combines rows from two tables based on a related column.

LEFT JOIN: Includes all rows from the left table and matched rows from the right table.

RIGHT JOIN: Includes all rows from the right table and matched rows from the left table.

FULL OUTER JOIN: Includes all rows from both tables, with NULLs in places where there is no match.

CROSS JOINS: Relational Databases

Frequently Asked Questions (FAQ) - SQL CROSS JOIN

1. What is a CROSS JOIN in SQL?

A CROSS JOIN in SQL produces a result set that is the Cartesian Product of the two tables involved, meaning every row from the first table is combined with every row from the second table.

2. How does a SQL CROSS JOIN differ from an SQL INNER JOIN?

A CROSS JOIN without a WHERE clause results in a Cartesian Product, whereas an INNER JOIN matches rows based on a specified condition. If a WHERE clause is used with a CROSS JOIN, it functions similarly to an INNER JOIN.

3. When should we use a SQL CROSS JOIN?

Use a CROSS JOIN to generate combinations of all rows between two tables for analysis or when we need to perform operations on every combination of records.

4. What are the performance considerations for using SQL CROSS JOINs?

CROSS JOINs can produce very large result sets, which may impact performance. It is advisable to be cautious with large tables and prefer using specific join conditions to limit the result set.

5. How does a SQL CROSS JOIN compare with other types of joins?

INNER JOIN: Combines rows from two tables based on a related column.

LEFT JOIN: Includes all rows from the left table and matched rows from the right table.

RIGHT JOIN: Includes all rows from the right table and matched rows from the left table.

FULL OUTER JOIN: Includes all rows from both tables, with NULLs in places where there is no match.

6. Which relational databases support SQL CROSS JOIN?

CROSS JOIN is supported by various relational databases, including Oracle, MySQL, PostgreSQL, and SQLite.

7. What are key points to remember about SQL CROSS JOIN?

Remember that a CROSS JOIN creates a Cartesian Product and can lead to large result sets. Use it when you need all combinations of rows between tables, and consider the impact on performance with large datasets.

Key points to remember

Click on the following to get the slides 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.

Previous: SQL NATURAL JOIN
Next:SQL OUTER JOIN