CROSS JOIN cartesian product in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does a CROSS JOIN do in SQL?

A CROSS JOIN returns the Cartesian product of two tables, combining every row from the first table with every row from the second table.

beginner

How many rows will the result have when you CROSS JOIN two tables with 3 and 4 rows respectively?

The result will have 3 × 4 = 12 rows, because each row from the first table pairs with every row from the second table.

beginner

Write a simple SQL query using CROSS JOIN for tables A and B.

SELECT * FROM A CROSS JOIN B;

intermediate

Why should you be careful when using CROSS JOIN in large tables?

Because CROSS JOIN multiplies rows from both tables, it can create a very large result set that may slow down your database or use a lot of memory.

intermediate

Is a WHERE clause commonly used with CROSS JOIN? Why or why not?

Yes, a WHERE clause is often used after a CROSS JOIN to filter the large result set and get meaningful combinations instead of all possible pairs.

What is the result of a CROSS JOIN between two tables?

A Rows from the first table only

B Only matching rows based on a condition

C Every row from the first table combined with every row from the second table

D Rows from the second table only

A CROSS JOIN returns the Cartesian product, pairing every row from the first table with every row from the second.

If table X has 5 rows and table Y has 10 rows, how many rows will SELECT * FROM X CROSS JOIN Y return?

A 5

B 50

C 10

D 15

The result is 5 × 10 = 50 rows because CROSS JOIN pairs every row from X with every row from Y.

Which SQL keyword is used to perform a Cartesian product?

A CROSS JOIN

B LEFT JOIN

C FULL JOIN

D INNER JOIN

CROSS JOIN is the SQL keyword that produces the Cartesian product of two tables.

What is a common use case for CROSS JOIN?

A To generate all possible combinations of two sets of data

B To find matching rows between tables

C To delete rows from a table

D To update rows in a table

CROSS JOIN is used to create all possible pairs between two tables, useful for combinations.

How can you reduce the large result set from a CROSS JOIN?

A Use ORDER BY

B Use SELECT DISTINCT

C Use GROUP BY without conditions

D Use a WHERE clause to filter rows

A WHERE clause filters the Cartesian product to only the rows you want.

Explain what a CROSS JOIN does and give an example of when you might use it.

Think about pairing every item from one list with every item from another.

Describe the potential performance impact of using CROSS JOIN on large tables and how to manage it.

Consider what happens when you multiply big numbers of rows.