Unit 5 · JOIN … ON

Data files / 資料檔：students, products, sales — three separate tables, used for both the worked examples and ClassWork 5.

• students
• products
• sales

Table Reference / 資料表參考

• students: student_id, name
• products: product_id, product_name, price
• sales: sale_id, student_id, product_id, quantity

三張表以 student_id 及 product_id 互相關聯。

Start Here / 開始做題

1. Identify which tables hold the columns you need. 先確認需要的欄位分別在哪張表。
2. Find the shared key column that links each pair of tables (e.g. student_id). 找出兩表共有的關聯欄位。
3. Give each table a short alias (s, p, sa) so column names stay readable. 用簡短別名區分欄位來源。
4. Decide JOIN (only matching rows) vs LEFT JOIN (keep all rows from the left table, even without a match). 決定要用 JOIN（只留有對應的資料）還是 LEFT JOIN（保留左表全部資料）。

Unit Dashboard / 單元地圖

Quick Concepts / 重點

SQL Syntax / SQL 語法

This is a complete three-table join. Copy it and change the columns. 以下是完整的三表連接查詢，可修改欄位再執行。

SELECT s.name, p.product_name, sa.quantity
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

Yellow Syntax Guide / 黃色語法重點

Dry Run / 手算追蹤

Sample rows (illustrative only / 僅供示範):

students: (1, Alice), (2, Ben) products: (1, Pen), (2, Book) sales: (student_id=1, product_id=2, quantity=3)

Joining on the matching ids gives: Alice, Book, 3. 以 student_id 及 product_id 分別對應到姓名與商品名稱，組出一列完整資料。

Examples / 例題

Example 5-1 · Students Who Made a Purchase

題目說明 / Problem Statement

Show students who made a purchase, with the product name and quantity. 只顯示有購買紀錄的學生。

Reference Answer / 參考答案

SELECT s.name, p.product_name, sa.quantity
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

Example 5-2 · All Students, Including Non-buyers

題目說明 / Problem Statement

Show all students, including those with no purchases. 即使學生沒有購物紀錄，也會顯示。

Reference Answer / 參考答案

SELECT s.name, p.product_name, sa.quantity
FROM students s
LEFT JOIN sales sa ON s.student_id = sa.student_id
LEFT JOIN products p ON sa.product_id = p.product_id;

Example 5-3 · Who Bought What, and Total Cost

題目說明 / Problem Statement

Show who bought what, how many, and the total cost (price × quantity). 顯示學生、商品、購買數量與總金額。

Reference Answer / 參考答案

SELECT s.name, p.product_name, p.price, sa.quantity,
       (p.price * sa.quantity) AS total
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

ClassWork 5 / 課後練習

Student task / 學生任務：先自行完成 SQL，再用每題下方的截圖核對結果。

ClassWork 5-1 · Sales with Names

List all sales with student name and product name. 顯示所有購買資料，學生與商品名稱。

Reference Answer / 參考答案

ClassWork 5-2 · Total Items per Student

Show each student's total items bought. 每位學生買了多少件商品。

Reference Answer / 參考答案

ClassWork 5-3 · Total Quantity per Product

Show each product's total quantity sold. 各商品的銷售數量。

Reference Answer / 參考答案

ClassWork 5-4 · Total Spent per Student (Buyers Only)

Show each student's name and the total money they spent (price × quantity) — only students who made a purchase. 顯示每位有購物紀錄的學生姓名與總花費金額（單價 × 數量）。

Reference Answer / 參考答案

ClassWork 5-5 · Total Spent per Student (All Students)

Show every student's name and total money spent, including students who bought nothing (show 0 instead of blank). 顯示所有學生姓名與總花費金額，即使沒有購物紀錄也顯示為 0。

Reference Answer / 參考答案

ClassWork 5-6 · Products Never Sold

Show all products even if not sold. 顯示所有商品（包含沒被賣出）。

Reference Answer / 參考答案

Visual: Matching Rows / 配對資料列

Learning objective: predict which rows remain after JOIN and LEFT JOIN.

Active Recall / 主動回想

• What shared key connects the two tables in a JOIN?
• Which join keeps a student who has no purchase?
• What does NULL mean in a LEFT JOIN result?

Spaced Review / 間隔複習

• Next lesson: sketch two tiny tables and predict the result of each join.
• 1 week later: write a query that finds left-table rows with no match.

Key Takeaways / 重點帶走

• A join connects rows through a shared key, not through row position.
• JOIN keeps matched rows; LEFT JOIN also keeps every left-table row.
• Table aliases make multi-table queries readable.

Common Mistakes / 常見錯誤

• Joining on unrelated columns or forgetting the ON condition.
• Using JOIN when the question requires records with no match.

30-Second Review / 30 秒複習

Draw two tables, circle their shared key, then say which rows a LEFT JOIN keeps.