← ACARA Digital Technologies Years 9–10

Table-qualified field names in a join

Names the table a field comes from, like students.name. Needed in a join when both tables share a field name, or SQL gives an ambiguous column error.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 40, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 72, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 40, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 112, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 37, QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 73, WA Computer Science ATAR Year 11 — Data Management — Module 76

Understanding Table-qualified field names in a join in ACARA Digital Technologies Years 9–10

What it does: studentId exists in both tables, so it is qualified as students.studentId. Using table.field removes the ambiguity. The foreign key orders.productId is matched to the primary key products.productId. Each name has its table in front, so SQL knows which column is which. Table names are required only when a field name is shared. Many programmers still add them to make the query clearer. The unqualified studentId could come from students or results. Writing students.studentId fixes the error.

Common mistakes: Both students and classes have a classId field, so a bare classId gives an ambiguous column error. It should be classes.classId. A table-qualified field uses a full stop (dot) between the table and the field, not a comma. It should be classes.room.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Table-qualified field names in a join to a class →

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 query avoids an ambiguous column error when both tables have a studentId field?
SELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;SELECT studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;
Matches
SELECT studentId, mark FROM students JOIN results ON students.studentId = results.studentId;
↓
studentId is in both tables but is not qualified in the SELECT, so SQL gives an ambiguous column error. It should be students.studentId.
Applying
Meanings
Term
table.field
Meaning
Names the table a field comes from, like students.name. Needed in a join when both tables share a field name, or SQL gives an ambiguous column error.
Practice
SELECT students.studentId, students.firstName, results.mark
Testing
Gaps
SELECT .name, classes.room
Check Answer
Spots
2classes.room,
3classId

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Giraffe' badge — High Perspective, one of Cloodit's 106 collectible badges
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.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which query avoids an ambiguous column error when both tables have a studentId field?
A. SELECT studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;
B. SELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId; ✓
studentId exists in both students and results, so SQL needs students.studentId to know which table to use.
2. What does students.firstName mean in a query?
A. The firstName field that comes from the students table ✓
B. A new table called students.firstName
The dot joins the table name and the field name. It tells SQL which table the field comes from.
3. True or false: you must write table.field for a field that exists in only one of the joined tables, like mark in results.
A. True: it is always required in a join
B. False: it is optional when the name is unique ✓
Table names are required only when a field name is shared. Many programmers still add them to make the query clearer.
4. Which error does SQL give when you use a field name that exists in both joined tables without a table name?
A. Ambiguous column name ✓
B. Table not found
SQL cannot tell which table's field you mean, so it reports an ambiguous column error.
5. Which is the correct way to write the table and field together?
A. SELECT students.firstName FROM students; ✓
B. SELECT students,firstName FROM students;
Use a full stop (dot) between the table name and the field name, not a comma. A comma would mean two separate items.

Match the Pairs

students.firstName → Names the students table as the source of the firstName field, so there is no doubt which table it comes from.
SELECT students.firstName, results.mark → Selects one field from each of two tables, with each field qualified by its own table name.
Both students and results have a studentId field, so the join must say which one it means or SQL gives an ambiguous column error. → SELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;
Qualifying the fields on both sides of the ON condition names the table for each side of the match. → ON orders.productId = products.productId
SELECT studentId, mark FROM students JOIN results ON students.studentId = results.studentId; → studentId is in both tables but is not qualified in the SELECT, so SQL gives an ambiguous column error. It should be students.studentId.
SELECT students.firstName, results.mark FROM students JOIN results ON studentId = studentId; → The ON condition uses studentId on both sides without a table name, so it is ambiguous. It should be students.studentId = results.studentId.

Key Term

table.field
Names the table a field comes from, like students.name. Needed in a join when both tables share a field name, or SQL gives an ambiguous column error.

Fill in the Blanks

1. SELECT students.name, classes.room FROM students JOIN classes ON students.classId = classes.classId;
Hint: Type the table that the name field comes from.
2. SELECT students.name, classes.room FROM students JOIN classes ON students.classId = classes.classId;
Hint: Type the field, from the classes table, that holds where the class is held.
3. SELECT students.name, classes.room FROM students JOIN classes ON students.classId = classes.classId;
Hint: Type the table that the matching key on the right side of the ON condition belongs to.
4. SELECT students.classId, classes.room FROM students JOIN classes ON students.classId = classes.classId;
Hint: Type the field name that exists in both tables, so it must be qualified to avoid an ambiguous column error.
5. SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results.studentId;
Hint: Type the shared key field that links the two tables, written after the results table name.

Spot the Error

Question 1
A SELECT students.name,
B classes.room,
C classId
D FROM students JOIN classes
E ON students.classId =
F classes.classId;
Line C — Both students and classes have a classId field, so a bare classId gives an ambiguous column error. It should be classes.classId.
Question 2
A SELECT students.name,
B classes,room
C FROM students JOIN classes
D ON students.classId =
E classes.classId;
Line B — A table-qualified field uses a full stop (dot) between the table and the field, not a comma. It should be classes.room.
Question 3
A SELECT productId,
B products.name, orders.quantity
C FROM orders JOIN products
D ON orders.productId =
E products.productId;
Line A — productId is in both orders and products, so an unqualified productId is an ambiguous column. It should be SELECT products.productId,
Question 4
A SELECT students.name,
B classes.room,
C students.teacher
D FROM students JOIN classes
E ON students.classId =
F classes.classId;
Line C — table.field must name the table the field really comes from. teacher is a field of classes, so it should be classes.teacher.
Question 5
A SELECT subjects.name
B FROM results JOIN subjects
C ON result.subjectId =
D subjects.subjectId;
Line C — The word before the dot must match the table name exactly. The table is results, so it should be ON results.subjectId =

Student with a class code? Join your class →