Why a query over two tables needs a join

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. An orders table stores only customer_id, and the customer's name is in a customers table. How do you show each order with the customer's name?

A Select from the orders table only, because the name is stored in it

B Join the two tables so each customer_id is matched to a customer

2. Which field acts as the bridge that links an order row to its customer row?

A The order date in orders

B The foreign key customer_id in orders

3. What does a join do with a foreign key value like customer_id = 3 in the orders table?

A It finds the customer whose primary key is 3 and shows the rows together

B It deletes the customer whose primary key is 3 from the customers table

4. Which query needs a join?

A List each order with the name of the customer who placed it

B List every order amount from the orders table

5. An order row has customer_id = 9, but no customer in the customers table has a primary key of 9. What happens in a join?

A That order row is left out of the results

B That order row shows up with a random customer name

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1orders(order_id, customer_id, total)		AThe name field is not in the orders table, which holds only customer_id. The query needs a JOIN to customers.
2customers(customer_id, name, city)		BON orders.customer_id = customers.customer_id
3To show each order with the customer's name, join the two tables so matching rows appear together		CSELECT customers.name, orders.total FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
4The foreign key is the bridge: a join matches it to the primary key in the other table		DThe ON condition matches the wrong field. It should match the foreign key orders.customer_id to customers.customer_id.
5SELECT name, total FROM orders;		EThe customer name lives in this table, where customer_id is the primary key
6FROM orders JOIN customers ON orders.order_id = customers.customer_id		FThe orders table holds only a customer_id, not the customer's name

Definitions

JOIN

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT orders.orderId, students.firstName FROM orders ____ students ON orders.studentId = students.studentId

SELECT orders.orderId, students.firstName FROM orders JOIN students __ orders.studentId = students.studentId

SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders._________ = students.studentId

SELECT orders.orderId, students.firstName FROM orders JOIN students ON orders.studentId _ students.studentId

SELECT orders.orderId, students.firstName FROM orders JOIN ________ ON orders.studentId = students.studentId

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT c.name, o.total

B FROM customers c

C JOIN customers o

D ON c.id = o.customer_id

Question 2

A SELECT o.order_id, o.name

B FROM orders o

C JOIN customers c

D ON o.customer_id = c.id

Question 3

A SELECT c.name, o.total

B FROM order o

C JOIN customers c

D ON o.customer_id = c.id

Question 4

A SELECT c.name, o.total

B FROM orders c

C JOIN customers c

D ON o.customer_id = c.id

E WHERE o.total > 50

Question 5

A SELECT c.name, o.total

B FROM customers c JOIN orders o

C ON c.id = o.order_id

D WHERE o.total > 50

Answer Key

Why a query over two tables needs a join

Section A: Quick Questions

B — Join the two tables so each customer_id is matched to a customer

B — The foreign key customer_id in orders

A — It finds the customer whose primary key is 3 and shows the rows together

A — List each order with the name of the customer who placed it

A — That order row is left out of the results

Section B: Match the Pairs

orders(order_id, customer_id, total) → The orders table holds only a customer_id, not the customer's name

customers(customer_id, name, city) → The customer name lives in this table, where customer_id is the primary key

To show each order with the customer's name, join the two tables so matching rows appear together → SELECT customers.name, orders.total FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

The foreign key is the bridge: a join matches it to the primary key in the other table → ON orders.customer_id = customers.customer_id

SELECT name, total FROM orders; → The name field is not in the orders table, which holds only customer_id. The query needs a JOIN to customers.

FROM orders JOIN customers ON orders.order_id = customers.customer_id → The ON condition matches the wrong field. It should match the foreign key orders.customer_id to customers.customer_id.

Section C: Definitions

JOIN — A join matches each foreign key value in one table to the primary key value in another, so rows from both tables show together. Rows with no match are left out.

Section D: Fill in the Blanks

SELECT orders.orderId, students.firstName
FROM orders JOIN students
ON orders.studentId = students.studentId

SELECT orders.orderId, students.firstName
FROM orders JOIN students
ON orders.studentId = students.studentId

SELECT orders.orderId, students.firstName
FROM orders JOIN students
ON orders.studentId = students.studentId

SELECT orders.orderId, students.firstName
FROM orders JOIN students
ON orders.studentId = students.studentId

SELECT orders.orderId, students.firstName
FROM orders JOIN students
ON orders.studentId = students.studentId

Section E: Spot the Error

C — The join must bring in the orders table, which holds customer_id and total. Joining customers to itself matches nothing useful. It should be JOIN orders o.

A — The orders table only holds customer_id. The customer's name lives in the customers table, so it must be selected as c.name after the join.

B — The table is called orders, not order. A query can only join tables that exist, so the line should be FROM orders o.

B — The alias c is already given to customers on line 3. Using c for orders as well makes the columns unclear. Orders should use the alias o: FROM orders o.

C — A join must match the foreign key to the primary key. o.order_id is not the foreign key, so customers would be matched to the wrong orders. It should be ON c.id = o.customer_id.