Writing the ON condition: foreign key equals primary key
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which ON condition correctly joins orders to customers?
2. In ON orders.customer_id = customers.customer_id, which side is the foreign key?
3. What does the = sign do in an ON condition?
4. Which ON condition is written in the correct form: table.field = table.field?
5. Which pair of fields should be compared in the ON condition?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1ON orders.customer_id = customers.customer_id | AFROM orders JOIN customers ON orders.customer_id = customers.customer_id | |
| 2orders.customer_id = customers.customer_id | BON orders.customer_id = customers.customer_id | |
| 3The 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. | CSQL 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. | |
| 4A complete join names both tables and then links them by comparing the foreign key with the primary key. | DMatches each order row to the customer row whose customer_id is the same, linking the foreign key to the primary key. | |
| 5ON orders.order_id = customers.customer_id | EThis compares the orders primary key with the customers primary key. It should compare the foreign key orders.customer_id with customers.customer_id. | |
| 6ON orders.customer_id == customers.customer_id | FThe = sign compares the two values to check if they are equal. It does not store or assign a value. |
C
Definitions
ON orders.customer_id = customers.customer_id
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT * FROM orders JOIN customers
__ orders.customer_id =
customers.customer_id
2.
SELECT * FROM orders JOIN customers
ON ______.customer_id =
customers.customer_id
3.
SELECT * FROM orders JOIN customers
ON orders.___________ =
customers.customer_id
4.
SELECT * FROM results JOIN students
ON results.studentId _
students.studentId
5.
SELECT * FROM customers JOIN orders
ON customers.___________ =
orders.customer_id
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT name, order_date
B
FROM orders JOIN customers
C
ON orders.order_id
D
= customers.customer_id
Question 2
A
SELECT customers.name
B
FROM customers JOIN orders
C
ON customers.customer_id
D
== 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;
Question 4
A
SELECT * orders
B
JOIN customers
C
ON orders.customer_id
D
= customers.customer_id;
Question 5
A
SELECT orders.order_id, name
B
FROM orders JOIN products
C
WHERE orders.product_id
D
= products.product_id
Answer Key
Writing the ON condition: foreign key equals primary key
Section A: Quick Questions
Q1
A — ON orders.customer_id = customers.customer_id
Q2
A — orders.customer_id is the foreign key
Q3
B — It compares two values to find matching rows
Q4
B — ON orders.customer_id = customers.customer_id
Q5
A — A foreign key and the primary key it refers to
Section B: Match the Pairs
Q1
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.
Q2
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.
Q3
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
Q4
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
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
SELECT * FROM orders JOIN customers
ON orders.customer_id =
customers.customer_idQ2
SELECT * FROM orders JOIN customers
ON orders.customer_id =
customers.customer_idQ3
SELECT * FROM orders JOIN customers
ON orders.customer_id =
customers.customer_idQ4
SELECT * FROM results JOIN students
ON results.studentId =
students.studentIdQ5
SELECT * FROM customers JOIN orders
ON customers.customer_id =
orders.customer_idSection E: Spot the Error
Q1
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.
Q2
D — SQL compares values with a single = sign. Double == is not valid in the ON condition. It should be = orders.customer_id.
Q3
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.
Q4
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.
Q5
C — A join needs the keyword ON to match the foreign key to the primary key. WHERE should be replaced with ON.