Understanding A join that forgets the ON condition in ACARA Digital Technologies Years 9–10
What it does: Without ON, the row count is rows x rows. Counting the rows of the unconditioned join shows the multiplication, e.g. 10 students x 10 orders = 100. The ON condition links the foreign key orders.productId to the primary key products.productId, so only matching rows are paired. The first query has no ON condition, so each student row is paired with every results row. A sudden jump in row count is the warning sign of a join with no ON condition. The database does not invent data. Join on the foreign key and the primary key it points to. Quantity and points are unrelated fields, so matches would be meaningless.
Common mistakes: 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). 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.
Quick Questions
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)
With no ON condition, every customer is paired with every order, so you get 3 x 4 = 12 rows.
2. Which query forgets the ON condition?
A. SELECT * FROM students JOIN results ON students.studentId = results.studentId;
B. SELECT * FROM students JOIN results; ✓
The first query has no ON condition, so each student row is paired with every results row.
3. The students table has 10 rows and the results table has 20 rows. A join with no ON condition gives how many rows?
10 x 20 = 200. Every student is paired with every result, and most of those pairs are wrong.
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
A sudden jump in row count is the warning sign of a join with no ON condition. The database does not invent 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 ✓
ON orders.studentId = students.studentId keeps only the pairs that really belong together. ORDER BY only changes the order, not the number of rows.
Fill in the Blanks
1. SELECT firstName, orderId
FROM students JOIN orders
ON students.studentId = orders.studentId;
Hint: Type the keyword that starts the matching condition in a join, so rows are only paired when the fields agree.
2. SELECT * FROM students JOIN orders;
-- 3 rows x 4 rows gives 12 rows
Hint: Type the number of rows you get by multiplying the row counts of the two tables.
3. SELECT name, quantity
FROM products JOIN orders
ON products.productId = orders.productId;
Hint: Type the foreign key field in orders that links back to the products table.
4. SELECT name, mark
FROM results JOIN subjects
ON results.subjectId = subjects.subjectId;
Hint: Type the table name that holds the foreign key field being matched.
5. SELECT COUNT(*) FROM students JOIN orders;
-- no ON, so every pairing is counted
Hint: Type the aggregate function that counts rows, which shows far too many rows when the ON condition is missing.
Spot the Error
Question 1
A
SELECT c.name, o.orderId
B
FROM customers c
C
JOIN orders o
D
ON c.id = o.orderId;
Line 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).
Question 2
A
SELECT s.firstName, o.orderId
B
FROM students s
C
JOIN orders o
D
ON s.studentId = s.studentId;
Line 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.
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;
Line 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.
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;
Line 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.
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;
Line 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.
