← ACARA Digital Technologies Years 9–10

Writing the ON condition: foreign key equals primary key

The join condition: it matches the foreign key in one table to the primary key in the other. The = sign compares the two values to find matching rows. It does not assign a value.

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

Understanding Writing the ON condition: foreign key equals primary key in ACARA Digital Technologies Years 9–10

What it does: orders.customer_id is the foreign key and customers.customer_id is the primary key. The ON condition sets them equal to match rows. In a join, = is a comparison. Rows are matched when the two values are equal. Nothing is stored or changed. Both tables have a field called customer_id, so you must put the table name before each field. Otherwise SQL cannot tell which one you mean. SQL uses a single = to compare values. Double equals (==) is used in some other languages, but it is a syntax error in SQL. Equals works both ways, so the matched rows are the same. Writing the foreign key first is just a common habit that makes the link easier to read.

Common mistakes: orders.order_id is the primary key of orders, not the foreign key. The ON condition must compare the foreign key orders.customer_id with customers.customer_id. SQL compares values with a single = sign. Double == is not valid in the ON condition. It should be = orders.customer_id.

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 Writing the ON condition: foreign key equals primary key 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 ON condition correctly joins orders to customers?

ON orders.order_id = customers.customer_idON orders.customer_id = customers.customer_id

Matches

ON orders.order_id = customers.customer_id

↓

This compares the orders primary key with the customers primary key. It should compare the foreign key orders.customer_id with customers.customer_id.

Applying

Meanings

Term

ON orders.customer_id = customers.customer_id

Meaning

The join condition: it matches the foreign key in one table to the primary key in the other. The = sign compares the two values to find matching rows. It does not assign a value.

Practice

SELECT students.firstName, orders.quantity

Testing

Gaps

orders.customerid =

Check Answer

Spots

2FROM orders JOIN customers

3ON orders.order_id

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 ON condition correctly joins orders to customers?

A. ON orders.customer_id = customers.customer_id ✓

B. ON orders.order_id = customers.customer_id

The ON condition matches the foreign key (orders.customer_id) to the primary key (customers.customer_id). order_id is the primary key of orders, so it does not link to customers.

2. In ON orders.customer_id = customers.customer_id, which side is the foreign key?

A. orders.customer_id is the foreign key ✓

B. customers.customer_id is the foreign key

The orders table holds a customer_id that points to a customer, so it is the foreign key. In customers, customer_id is the primary key.

3. What does the = sign do in an ON condition?

A. It assigns the customer_id from one table into the other

B. It compares two values to find matching rows ✓

In a join, = is a comparison. Rows are matched when the two values are equal. Nothing is stored or changed.

4. Which ON condition is written in the correct form: table.field = table.field?

A. ON customer_id = customer_id

B. ON orders.customer_id = customers.customer_id ✓

Both tables have a field called customer_id, so you must put the table name before each field. Otherwise SQL cannot tell which one you mean.

5. Which pair of fields should be compared in the ON condition?

A. A foreign key and the primary key it refers to ✓

B. Any two fields that hold numbers

Matching rows only makes sense when the foreign key is compared with the primary key it points to. Two unrelated number fields would give meaningless matches.

Match the Pairs

ON orders.customer_id = customers.customer_id → Matches each order row to the customer row whose customer_id is the same, linking the foreign key to the primary key.

orders.customer_id = customers.customer_id → The = sign compares the two values to check if they are equal. It does not store or assign a value.

The foreign key is in the orders table, so it is written on one side of the = and the primary key from customers goes on the other. → ON orders.customer_id = customers.customer_id

A complete join names both tables and then links them by comparing the foreign key with the primary key. → FROM orders JOIN customers ON orders.customer_id = customers.customer_id

ON orders.order_id = customers.customer_id → This compares the orders primary key with the customers primary key. It should compare the foreign key orders.customer_id with customers.customer_id.

ON orders.customer_id == customers.customer_id → SQL uses a single = to compare values in an ON condition. A double == is not valid SQL, so it must be ON orders.customer_id = customers.customer_id.

Key Term

ON orders.customer_id = customers.customer_id

The join condition: it matches the foreign key in one table to the primary key in the other. The = sign compares the two values to find matching rows. It does not assign a value.

Fill in the Blanks

1. SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id

Hint: Type the keyword that starts the join condition.

2. SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id

Hint: Type the name of the table that holds the foreign key.

3. SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id

Hint: Type the foreign key field in the orders table.

4. SELECT * FROM results JOIN students ON results.studentId = students.studentId

Hint: Type the operator that compares the foreign key with the primary key to find matching rows.

5. SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id

Hint: Type the primary key field of the customers table.

Spot the Error

Question 1

A SELECT name, order_date

B FROM orders JOIN customers

C ON orders.order_id

D = customers.customer_id

Line C — orders.order_id is the primary key of orders, not the foreign key. The ON condition must compare the foreign key orders.customer_id with customers.customer_id.

Question 2

A SELECT customers.name

B FROM customers JOIN orders

C ON customers.customer_id

D == orders.customer_id

Line D — SQL compares values with a single = sign. Double == is not valid in the ON condition. It should be = orders.customer_id.

Question 3

A SELECT order_id, name

B FROM orders

C JOIN customers

D ON orders.customer_id :=

E customers.customer_id;

Line D — The ON condition compares two values, it does not assign one. Use = instead of :=, so it reads ON orders.customer_id = customers.customer_id.

Question 4

A SELECT * orders

B JOIN customers

C ON orders.customer_id

D = customers.customer_id;

Line A — The FROM keyword is missing. The query must name the first table with FROM orders before it can JOIN customers ON the matching keys.

Question 5

A SELECT orders.order_id, name

B FROM orders JOIN products

C WHERE orders.product_id

D = products.product_id

Line C — A join needs the keyword ON to match the foreign key to the primary key. WHERE should be replaced with ON.

Student with a class code? Join your class →