A join that forgets the ON condition

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A query joins a table of 3 customers with a table of 4 orders but has no ON condition. How many rows come back?

A 12 rows (3 x 4)

B 4 rows (one per order)

2. Which query forgets the ON condition?

A SELECT * FROM students JOIN results ON students.studentId = results.studentId;

B SELECT * FROM students JOIN results;

3. The students table has 10 rows and the results table has 20 rows. A join with no ON condition gives how many rows?

A 200 rows

B 20 rows

4. You run a join and get far more rows than the orders table holds. What is the most likely cause?

A The ON condition is missing, so every row is paired with every other row

B The database has added extra orders to the data

5. How do you fix a join that gives too many rows?

A Add ORDER BY to sort out the extra rows

B Add ON with the foreign key equal to the primary key

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT * FROM students JOIN orders;		AA join with no ON condition: every student row is paired with every order row, so most result rows are wrong
2SELECT * FROM students JOIN orders ON students.studentId = orders.studentId;		BSELECT COUNT(*) FROM students JOIN orders;
33 customers joined to 4 orders with no ON condition gives 3 x 4 = 12 rows		CFROM orders JOIN products ON orders.productId = products.productId
4Adding the ON condition stops every row being paired with every other row		DA join with an ON condition: only rows where the student IDs match are kept, so each order pairs with its own student
5SELECT firstName, name FROM students JOIN products;		EThe ON condition joins the wrong fields. It should match students.studentId = orders.studentId (primary key to foreign key).
6SELECT * FROM students JOIN orders ON students.points = orders.quantity;		FThe ON condition is missing, so every student is paired with every product and most rows are wrong. Add ON to match the linked fields.

Definitions

Cartesian product

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT firstName, orderId FROM students JOIN orders __ students.studentId = orders.studentId;

SELECT * FROM students JOIN orders; -- 3 rows x 4 rows gives __ rows

SELECT name, quantity FROM products JOIN orders ON products.productId = orders._________;

SELECT name, mark FROM results JOIN subjects ON _______.subjectId = subjects.subjectId;

SELECT _____(*) FROM students JOIN orders; -- no ON, so every pairing is counted

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT c.name, o.orderId

B FROM customers c

C JOIN orders o

D ON c.id = o.orderId;

Question 2

A SELECT s.firstName, o.orderId

B FROM students s

C JOIN orders o

D ON s.studentId = s.studentId;

Question 3

A -- 3 customers, 4 orders

B -- No ON: 3 + 4 = 7 rows

C SELECT c.name, o.orderId

D FROM customers c JOIN orders o

E ON c.id = o.customerId;

Question 4

A -- 3 customers, 4 orders

B -- No ON: only matches pair up

C SELECT c.name, o.orderId

D FROM customers c JOIN orders o

E ON c.id = o.customerId;

Question 5

A -- Too many rows? Drop the ON

B SELECT c.name, o.orderId

C FROM customers c JOIN orders o

D ON c.id = o.customerId;

Answer Key

A join that forgets the ON condition

Section A: Quick Questions

A — 12 rows (3 x 4)

B — SELECT * FROM students JOIN results;

A — 200 rows

A — The ON condition is missing, so every row is paired with every other row

B — Add ON with the foreign key equal to the primary key

Section B: Match the Pairs

SELECT * FROM students JOIN orders; → A join with no ON condition: every student row is paired with every order row, so most result rows are wrong

SELECT * FROM students JOIN orders ON students.studentId = orders.studentId; → A join with an ON condition: only rows where the student IDs match are kept, so each order pairs with its own student

3 customers joined to 4 orders with no ON condition gives 3 x 4 = 12 rows → SELECT COUNT(*) FROM students JOIN orders;

Adding the ON condition stops every row being paired with every other row → FROM orders JOIN products ON orders.productId = products.productId

SELECT firstName, name FROM students JOIN products; → The ON condition is missing, so every student is paired with every product and most rows are wrong. Add ON to match the linked fields.

SELECT * FROM students JOIN orders ON students.points = orders.quantity; → The ON condition joins the wrong fields. It should match students.studentId = orders.studentId (primary key to foreign key).

Section C: Definitions

Cartesian product — A join with no ON condition pairs every row of one table with every row of the other, so 3 customers and 4 orders give 12 rows, most of them wrong.

Section D: Fill in the Blanks

SELECT firstName, orderId
FROM students JOIN orders
ON students.studentId = orders.studentId;

SELECT * FROM students JOIN orders;
-- 3 rows x 4 rows gives 12 rows

SELECT name, quantity
FROM products JOIN orders
ON products.productId = orders.productId;

SELECT name, mark
FROM results JOIN subjects
ON results.subjectId = subjects.subjectId;

SELECT COUNT(*) FROM students JOIN orders;
-- no ON, so every pairing is counted

Section E: Spot the Error

D — The ON condition compares the customer's id with the order's own id, not the customer it belongs to. The rows are paired on the wrong fields. It should be ON c.id = o.customerId; (primary key = foreign key).

D — This compares the students table with itself, so it is true for every row. Every student is still paired with every order. It should be ON s.studentId = o.studentId; to link students to orders.

B — Without an ON condition, every customer is paired with every order, so the rows are multiplied, not added. 3 x 4 = 12 rows, not 7.

B — With no ON condition nothing filters the pairs, so every row pairs with every row, matching or not. Only the ON condition keeps just the matching rows.

A — Too many rows means the join has no matching condition. The fix is to add the ON condition, not to remove it. Dropping it would give 3 x 4 = 12 rows again.