SQL Cross Join
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.
Visual 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
+---------+--------------+-----------+------------+ | 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, -- 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.
or
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