← ACARA Digital Technologies Years 9–10

Table aliases in a join

Gives the students table the short name s after FROM, so the rest of the query can write s.firstName instead of students.firstName.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 41, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 73, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 41, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 113, WA Computer Science ATAR Year 11 — Data Management — Module 77

Understanding Table aliases in a join in ACARA Digital Technologies Years 9–10

What it does: s.firstName and r.mark use the aliases defined in FROM and JOIN instead of the full table names. Using s and r is much quicker to type than students.studentId = results.studentId, and it means the same thing. The table name comes first, then the alias: FROM students s. The alias is the short name that follows the table. After an alias is set, use it everywhere in the query. Write s.firstName, not students.firstName.

Common mistakes: The students table was given the alias s in the FROM line, so the rest of the query must use s. The first line should be SELECT s.house, r.mark. The products table was given the alias p, but the SELECT uses pr, which was never defined. It should be p.name.

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 aliases 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 line gives the students table the short name s?

FROM students sFROM s students

Matches

SELECT students.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId

↓

The students table was given the alias s, but the SELECT still uses the full name students. Use s.firstName so the alias is used everywhere.

Applying

Meanings

Term

FROM students s

Meaning

Gives the students table the short name s after FROM, so the rest of the query can write s.firstName instead of students.firstName.

Practice

SELECT s.firstName, r.mark

Testing

Gaps

FROM students

Check Answer

Spots

1SELECT students.house, r.mark

2FROM students s

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.

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 line gives the students table the short name s?

A. FROM students s ✓

B. FROM s students

The table name comes first, then the alias: FROM students s. The alias is the short name that follows the table.

2. Once you write FROM students s, how should you refer to the firstName column?

A. students.firstName

B. s.firstName ✓

After an alias is set, use it everywhere in the query. Write s.firstName, not students.firstName.

3. Which ON condition is shorter and uses aliases correctly? (students s, results r)

A. ON students.studentId = results.studentId

B. ON s.studentId = r.studentId ✓

Aliases make the ON condition shorter. Both versions describe the same link, but s and r are much quicker to type.

4. True or false: you can set an alias in the FROM clause and still write the full table name in the SELECT.

A. False ✓

B. True

Once a table has an alias, the full table name no longer works for that table in the query. Use the alias everywhere.

5. Which query is written correctly?

A. SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId ✓

B. SELECT s.firstName, r.mark FROM students JOIN results ON s.studentId = r.studentId

The aliases s and r must be set after the table names in FROM and JOIN before they are used in SELECT or ON.

Match the Pairs

FROM students s → Gives the students table the short name s (an alias) so the rest of the query can use s instead of students

JOIN results r ON s.studentId = r.studentId → Joins the results table, calls it r, and uses the short names s and r to match the foreign key to the primary key

After you give a table an alias, use that alias to qualify its columns in the SELECT list → SELECT s.firstName, r.mark

An alias makes the ON condition shorter than using the full table names → ON s.studentId = r.studentId

SELECT students.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId → The students table was given the alias s, but the SELECT still uses the full name students. Use s.firstName so the alias is used everywhere.

SELECT s.firstName, r.mark FROM students s JOIN results ON s.studentId = r.studentId → The alias r is used in the ON condition, but it was never created after JOIN results. It should be JOIN results r.

Key Term

FROM students s

Gives the students table the short name s after FROM, so the rest of the query can write s.firstName instead of students.firstName.

Fill in the Blanks

1. SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Hint: Type the short alias given to the students table after its name.

2. SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Hint: Type the alias that stands for the students table before the dot.

3. SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Hint: Type the alias of the table that holds the primary key in the ON condition.

4. SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;

Hint: Type the short alias that names the results table in this join.

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

Hint: Type the alias of the products table to finish the ON condition.

Spot the Error

Question 1

A SELECT students.house, r.mark

B FROM students s

C JOIN results r

D ON s.studentId = r.studentId

Line A — The students table was given the alias s in the FROM line, so the rest of the query must use s. The first line should be SELECT s.house, r.mark.

Question 2

A SELECT o.orderId, pr.name

B FROM orders o

C JOIN products p

D ON o.productId = p.productId

Line A — The products table was given the alias p, but the SELECT uses pr, which was never defined. It should be p.name.

Question 3

A SELECT s.firstName, r.mark

B FROM students s

C JOIN r results

D ON s.studentId = r.studentId

Line C — The alias goes after the table name, not before it. The line should be JOIN results r.

Question 4

A SELECT s.firstName, r.mark

B FROM students s

C JOIN results r

D ON s.studentId = t.studentId

Line D — The alias t was never defined. The results table has the alias r, so the ON condition should be s.studentId = r.studentId.

Question 5

A SELECT s.firstName, r.mark

B FROM students, s

C JOIN results r

D ON s.studentId = r.studentId

E WHERE r.mark > 80

Line B — The alias follows the table name with just a space. The comma makes s look like a second table. It should be FROM students s.

Student with a class code? Join your class →