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.

Practice

1. What does a CROSS JOIN do in PostgreSQL?

easy

A. Pairs every row from the first table with every row from the second table.

B. Returns only matching rows based on a condition.

C. Joins tables based on a common column with duplicates removed.

D. Filters rows from the first table that exist in the second table.

Solution

1. Step 1: Understand CROSS JOIN concept

   A CROSS JOIN creates combinations of all rows from both tables without any condition.

2. Step 2: Compare with other JOIN types

   Unlike INNER JOIN or LEFT JOIN, CROSS JOIN does not filter or match rows but pairs all possible combinations.

3. Final Answer:

   Pairs every row from the first table with every row from the second table. -> Option A

4. Quick Check:

   CROSS JOIN = all combinations [OK]

Hint: Remember: CROSS JOIN multiplies rows from both tables [OK]

Common Mistakes:

• Confusing CROSS JOIN with INNER JOIN
• Thinking CROSS JOIN filters rows
• Assuming CROSS JOIN requires a join condition

2. Which of the following is the correct syntax for a CROSS JOIN in PostgreSQL?

easy

A. SELECT * FROM table1 CROSS JOIN table2;

B. SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

C. SELECT * FROM table1 INNER CROSS JOIN table2;

D. SELECT * FROM table1 CROSS JOIN ON table2;

Solution

1. Step 1: Recall CROSS JOIN syntax

   The correct syntax is SELECT * FROM table1 CROSS JOIN table2; without any ON clause.

2. Step 2: Identify incorrect options

   SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; uses INNER JOIN with ON clause, C has invalid keyword INNER CROSS JOIN, A has misplaced ON keyword.

3. Final Answer:

   SELECT * FROM table1 CROSS JOIN table2; -> Option A

4. Quick Check:

   Syntax for CROSS JOIN = SELECT * FROM table1 CROSS JOIN table2; [OK]

Hint: CROSS JOIN syntax has no ON clause [OK]

Common Mistakes:

• Adding ON clause with CROSS JOIN
• Using INNER CROSS JOIN which is invalid
• Misplacing keywords in the query

3. You have two tables:
Colors:
color_id | color_name
1 | Red
2 | Blue

Shapes:
shape_id | shape_name
1 | Circle
2 | Square
3 | Triangle

How many rows will this query return?
SELECT color_name, shape_name FROM Colors CROSS JOIN Shapes;

easy

A. 5

B. 3

C. 2

D. 6

Solution

1. Step 1: Count rows in each table

   Colors has 2 rows, Shapes has 3 rows.

2. Step 2: Calculate CROSS JOIN result size

   Result rows = 2 x 3 = 6 rows, all combinations of colors and shapes.

3. Final Answer:

   6 -> Option D

4. Quick Check:

   2 x 3 = 6 rows [OK]

Hint: Multiply row counts of both tables for CROSS JOIN size [OK]

Common Mistakes:

• Adding rows instead of multiplying
• Ignoring one table's row count
• Confusing CROSS JOIN with INNER JOIN

4. Given two tables:
Table A:
id | name
1 | Alice
2 | Bob

Table B:
code
X
Y

What is the result of:
SELECT A.name, B.code FROM A CROSS JOIN B;

medium

A. [('Alice', 'X'), ('Bob', 'Y')]

B. [('Alice', 'X'), ('Alice', 'Y'), ('Bob', 'X'), ('Bob', 'Y')]

C. [('Alice', 'X'), ('Bob', 'X'), ('Alice', 'Y'), ('Bob', 'Y')]

D. [('Alice', 'X'), ('Bob', 'X')]

Solution

1. Step 1: Understand CROSS JOIN output

   Each row in A pairs with every row in B, so 2 rows in A x 2 rows in B = 4 rows total.

2. Step 2: List all combinations

   Pairs are (Alice, X), (Alice, Y), (Bob, X), (Bob, Y) in that order.

3. Final Answer:

   [('Alice', 'X'), ('Alice', 'Y'), ('Bob', 'X'), ('Bob', 'Y')] -> Option B

4. Quick Check:

   2 x 2 = 4 rows [OK]

Hint: Multiply row counts of both tables for CROSS JOIN result size [OK]

Common Mistakes:

• Assuming CROSS JOIN filters rows
• Mixing order of pairs incorrectly
• Counting only matching rows instead of all combinations

5. Consider this query:
SELECT * FROM table1 CROSS JOIN table2 ON table1.id = table2.id;
What is the problem with this query?

medium

A. It behaves like INNER JOIN with ON condition.

B. It will return only matching rows based on id.

C. Syntax error because CROSS JOIN does not allow ON clause.

D. It returns all rows from table1 only.

Solution

1. Step 1: Check CROSS JOIN syntax rules

   CROSS JOIN syntax does not support ON clause; adding ON causes syntax error.

2. Step 2: Understand error cause

   ON clause is used with INNER or LEFT JOIN, not CROSS JOIN.

3. Final Answer:

   Syntax error because CROSS JOIN does not allow ON clause. -> Option C

4. Quick Check:

   CROSS JOIN + ON = syntax error [OK]

Hint: No ON clause allowed with CROSS JOIN [OK]

Common Mistakes:

• Thinking CROSS JOIN can have ON clause
• Confusing CROSS JOIN with INNER JOIN
• Expecting filtered results from CROSS JOIN