A join with WHERE and ORDER BY
After JOIN ... ON links two tables, WHERE filters the joined rows and ORDER BY sorts them. Clause order: SELECT, FROM, JOIN ON, WHERE, ORDER BY.
Understanding A join with WHERE and ORDER BY in ACARA Digital Technologies Years 9–10
What it does: This query puts the clauses in the right order: JOIN ... ON is before WHERE, and ORDER BY is at the very end. The WHERE clause uses products.category, a field from the second table, to keep only Stationery rows after the join. DESC sorts from largest to smallest. HIGHEST is not an SQL keyword.
Common mistakes: The ON condition must match the foreign key to the primary key it refers to. results.studentId links to students.studentId, so it should be ON s.studentId = r.studentId. A JOIN must be followed by ON, not WHERE. WHERE is saved for filtering the joined rows. The line should be ON p.productId = o.productId.
Class: _____________________ · Name: _____________________ · Date: _________
Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.
This module's key terms are also in a printable crossword (Modules 459–475) and word search (Modules 459–534).
Take this worksheet further — free with Cloodit
Each concept is reinforced from every angle — learned, then applied, then tested.
Learning
Duo
Which clause order is correct for a join query that filters and sorts?
SELECT, FROM, JOIN ... ON, WHERE, ORDER BYSELECT, FROM, ORDER BY, JOIN ... ON, WHERE
Matches
SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark WHERE results.mark > 50
↓
ORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.
Applying
Meanings
Term
JOIN ... ON ... WHERE ... ORDER BY
Meaning
After JOIN ... ON links two tables, WHERE filters the joined rows and ORDER BY sorts them. Clause order: SELECT, FROM, JOIN ON, WHERE, ORDER BY.
Practice
SELECT students.firstName, results.mark
Testing
Gaps
category = 'Tech' ORDER BY price
Check Answer
Spots
2FROM students s JOIN results r
3ON s.studentId = r.subjectId
Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.
Students earn collectable badges
Over 100 of them, across seven natural biomes.
Study Buddy
Students build a real study resource
Turns their work into revision material ready for Flash Cards and Exam study.
Students lock in with Sprints
Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.
Custom printable certificates
Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.
Quick Questions
1. Which clause order is correct for a join query that filters and sorts?
WHERE and ORDER BY come after the JOIN ... ON. WHERE filters the joined rows first, then ORDER BY sorts what is left.
2. Which query lists only students who bought a Notebook? (tables: orders, products)
WHERE can filter on a field from the second table, but only after the JOIN has brought that table in.
3. Which query sorts results by the subject name from the second table?
ORDER BY is the last clause. It sorts the joined rows, so it can use a field from any joined table.
4. True or false: in a join query, WHERE can filter on a field that comes from the second table.
Once the tables are joined, every field from both tables can be used in WHERE, for example WHERE products.category = 'Tech'.
5. Where does the WHERE clause go in a join query?
The JOIN ... ON must come straight after FROM. Then WHERE filters, then ORDER BY sorts.
Match the Pairs
SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId WHERE results.mark > 80
→
Joins students to results, then keeps only the joined rows where the mark (a field from the second table) is above 80
SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark DESC
→
Joins students to results, then sorts the joined rows by mark from highest to lowest
The correct clause order for a join query is SELECT, FROM, JOIN ... ON, WHERE, ORDER BY
→
SELECT students.firstName, subjects.name FROM results JOIN subjects ON results.subjectId = subjects.subjectId WHERE subjects.name = 'Maths' ORDER BY students.firstName
A join can be filtered using a field from the second table, so only matching joined rows are kept
→
SELECT products.name, orders.quantity FROM orders JOIN products ON orders.productId = products.productId WHERE products.category = 'Stationery'
SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId ORDER BY results.mark WHERE results.mark > 50
→
ORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.
SELECT students.firstName, results.mark FROM students JOIN results WHERE results.mark > 80 ON students.studentId = results.studentId
→
WHERE is placed before the ON condition. ON must directly follow JOIN, and WHERE must come after it.
Key Term
JOIN ... ON ... WHERE ... ORDER BYAfter JOIN ... ON links two tables, WHERE filters the joined rows and ORDER BY sorts them. Clause order: SELECT, FROM, JOIN ON, WHERE, ORDER BY.
Fill in the Blanks
1. SELECT name, price FROM orders JOIN products
ON orders.productId = products.productId
WHERE category = 'Tech' ORDER BY price
Hint: Type the keyword that filters the joined rows. It comes after the ON condition.
2. SELECT name, mark FROM results JOIN subjects
ON results.subjectId = subjects.subjectId
WHERE name = 'Maths' ORDER BY mark DESC
Hint: Type the two-word clause that sorts the joined rows. It goes last in the query.
3. SELECT name, mark FROM results JOIN subjects
ON results.subjectId = subjects.subjectId
WHERE name = 'Maths' ORDER BY mark DESC
Hint: Type the field from the second table (subjects) that the WHERE clause filters on.
4. SELECT name, price FROM orders JOIN products
ON orders.productId = products.productId
WHERE category = 'Tech' ORDER BY price
Hint: Type the field from the second table (products) that the rows are sorted by.
5. SELECT name, price FROM orders JOIN products
ON orders.productId = products.productId
WHERE category = 'Tech' ORDER BY price
Hint: Type the keyword that introduces the matching condition. It comes after JOIN and before WHERE.
Spot the Error
Question 1
A
SELECT s.firstName, r.mark
B
FROM students s JOIN results r
C
ON s.studentId = r.subjectId
D
WHERE r.mark > 80
E
ORDER BY r.mark DESC
Line C — The ON condition must match the foreign key to the primary key it refers to. results.studentId links to students.studentId, so it should be ON s.studentId = r.studentId.
Question 2
A
SELECT p.name, o.quantity
B
FROM products p JOIN orders o
C
WHERE p.productId = o.productId
D
WHERE o.quantity > 2
E
ORDER BY p.name
Line C — A JOIN must be followed by ON, not WHERE. WHERE is saved for filtering the joined rows. The line should be ON p.productId = o.productId.
Question 3
A
SELECT s.name, r.mark
B
FROM results r JOIN subjects s
C
ON r.subjectId = s.subjectId
D
WHERE s.name = Maths
E
ORDER BY r.mark DESC
Line D — Text values in a WHERE condition need quotes. Without them SQL reads Maths as a column name. It should be WHERE s.name = 'Maths'.
Question 4
A
SELECT s.firstName, r.mark
B
FROM students s JOIN results
C
ON s.studentId = r.studentId
D
WHERE r.mark < 50
E
ORDER BY r.mark
Line B — The query uses the alias r in SELECT, ON, WHERE and ORDER BY, but the alias was never given to results. It should be FROM students s JOIN results r.
Question 5
A
SELECT s.firstName r.mark
B
FROM students s JOIN results r
C
ON s.studentId = r.studentId
D
WHERE s.house = 'Red'
E
ORDER BY r.mark DESC
Line A — The fields in a SELECT list must be separated by commas. It should be SELECT s.firstName, r.mark.
Student with a class code? Join your class →