CROSS JOIN behavior in PostgreSQL - 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 a table with 3 rows and another with 4 rows?

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

beginner

True or False: A CROSS JOIN requires a join condition (ON clause).

False. A CROSS JOIN does not use a join condition; it simply pairs all rows from both tables.

intermediate

What is a real-life example of using a CROSS JOIN?

Imagine you want to create all possible combinations of T-shirt colors and sizes. Using CROSS JOIN on a colors table and a sizes table gives every color paired with every size.

intermediate

How can you limit the size of a CROSS JOIN result?

You can add a WHERE clause to filter rows after the CROSS JOIN or use LIMIT to restrict the number of rows returned.

What does a CROSS JOIN return?

A Only matching rows based on a condition

B All combinations of rows from both tables

C Rows from the first table only

D Rows from the second table only

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

If table A has 5 rows and table B has 2 rows, how many rows will a CROSS JOIN produce?

A 10

B 2

C 7

D 5

The result has 5 × 2 = 10 rows, pairing each row of A with each row of B.

Which SQL clause is NOT needed in a CROSS JOIN?

A SELECT

B FROM

C WHERE

D ON

CROSS JOIN does not require an ON clause because it does not use join conditions.

What is a potential risk of using CROSS JOIN without filtering?

A Getting syntax errors

B Getting fewer rows than expected

C Getting a very large result set that may slow down queries

D Losing data from tables

Because CROSS JOIN produces all combinations, it can create very large result sets if tables are big.

How can you reduce the number of rows returned by a CROSS JOIN?

A Use WHERE clause to filter results

B Use CROSS JOIN with ON clause

C Use only one table

D Use GROUP BY without filtering

Filtering with WHERE after CROSS JOIN helps limit the rows returned.

Explain in your own words what a CROSS JOIN does and when you might use it.

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

Describe the risks of using CROSS JOIN on large tables and how to manage those risks.

Consider what happens when you multiply big numbers of rows.