先預測結果，再看輸出。
SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。
01｜先理解資料與結果
INNER JOIN 只保留 ON 條件為真的配對。若一筆 reading 所在區域有兩個測站，這筆原紀錄會形成兩列；這是資料關係，不是錯誤重複。NULL zone 不會以 = 相互匹配。多表同名欄位需用 r.zone、s.zone 等表別名指出來源。
02｜建立正確查詢規則
CROSS JOIN 產生每個左列與每個右列的組合，n×m 列，任一側空表則沒有配對。若要統計原始 reading 數，COUNT(*) 在一對多連接後會計配對數；應依題意使用 COUNT(DISTINCT r.id) 或先摘要。不要用 DISTINCT 隱藏不正確的 ON 條件。
簡單範例｜測點配對
readings 與 stations 依 zone 相等連接；NULL zone 不匹配，包括兩側皆 NULL。一列若匹配多個測站，逐配對輸出，不去重。輸出每個同區域配對的兩個 id 與 zone。
先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。
範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}, {"name": "stations", "columns": [{"name": "station_id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "target", "type": "INTEGER", "nullable": true}, {"name": "enabled", "type": "INTEGER", "nullable": true}], "rows": [[8, "west", 0, null], [2, "north", 9, 1], [9, "", -2, 1], [11, null, 3, 1], [3, "south", null, 0], [14, "east", 1, 0], [5, "north", 5, 1]]}]}SQLite
SELECT reading_id AS reading_id, station_id AS station_id, zone AS zone
FROM (
SELECT r.id AS reading_id, s.station_id AS station_id, r.zone AS zone
FROM readings r JOIN stations s ON r.zone=s.zone
) query_result
ORDER BY reading_id ASC, station_id ASC;PostgreSQL
SELECT reading_id AS reading_id, station_id AS station_id, zone AS zone
FROM (
SELECT r.id AS reading_id, s.station_id AS station_id, r.zone AS zone
FROM readings r JOIN stations s ON r.zone=s.zone
) query_result
ORDER BY reading_id ASC, station_id ASC;MySQL
SELECT reading_id AS reading_id, station_id AS station_id, zone AS zone
FROM (
SELECT r.id AS reading_id, s.station_id AS station_id, r.zone AS zone
FROM readings r JOIN stations s ON r.zone=s.zone
) query_result
ORDER BY reading_id ASC, station_id ASC;預期查詢結果
reading_id | station_id | zone 2 | 2 | "north" 2 | 5 | "north" 3 | 2 | "north" 3 | 5 | "north" 4 | 8 | "west" 5 | 14 | "east" 7 | 3 | "south" 8 | 2 | "north" 8 | 5 | "north" 9 | 9 | "" 10 | 8 | "west" 11 | 3 | "south" 12 | 14 | "east"
中等範例｜目標缺漏表
readings 與 stations 依 zone 相等連接；NULL zone 不匹配，包括兩側皆 NULL。一列若匹配多個測站，逐配對輸出，不去重。只保留 target 為 NULL 的配對，reading 可以缺值。
先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。
範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}, {"name": "stations", "columns": [{"name": "station_id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "target", "type": "INTEGER", "nullable": true}, {"name": "enabled", "type": "INTEGER", "nullable": true}], "rows": [[8, "west", 0, null], [2, "north", 9, 1], [9, "", -2, 1], [11, null, 3, 1], [3, "south", null, 0], [14, "east", 1, 0], [5, "north", 5, 1]]}]}SQLite
SELECT reading_id AS reading_id, station_id AS station_id, reading AS reading, target AS target
FROM (
SELECT r.id AS reading_id, s.station_id AS station_id, r.reading AS reading, s.target AS target
FROM readings r JOIN stations s ON r.zone=s.zone
WHERE s.target IS NULL
) query_result
ORDER BY reading_id ASC, station_id ASC;PostgreSQL
SELECT reading_id AS reading_id, station_id AS station_id, reading AS reading, target AS target
FROM (
SELECT r.id AS reading_id, s.station_id AS station_id, r.reading AS reading, s.target AS target
FROM readings r JOIN stations s ON r.zone=s.zone
WHERE s.target IS NULL
) query_result
ORDER BY reading_id ASC, station_id ASC;MySQL
SELECT reading_id AS reading_id, station_id AS station_id, reading AS reading, target AS target
FROM (
SELECT r.id AS reading_id, s.station_id AS station_id, r.reading AS reading, s.target AS target
FROM readings r JOIN stations s ON r.zone=s.zone
WHERE s.target IS NULL
) query_result
ORDER BY reading_id ASC, station_id ASC;預期查詢結果
reading_id | station_id | reading | target 7 | 3 | -2 | NULL 11 | 3 | 9 | NULL
困難範例｜測站讀值和
先以 zone 相等做內部連接，NULL zone 不匹配。輸出每測站有效 reading 總和，無有效值為 0。
先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。
範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}, {"name": "stations", "columns": [{"name": "station_id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "target", "type": "INTEGER", "nullable": true}, {"name": "enabled", "type": "INTEGER", "nullable": true}], "rows": [[8, "west", 0, null], [2, "north", 9, 1], [9, "", -2, 1], [11, null, 3, 1], [3, "south", null, 0], [14, "east", 1, 0], [5, "north", 5, 1]]}]}SQLite
SELECT station_id AS station_id, total AS total
FROM (
SELECT s.station_id AS station_id, COALESCE(SUM(r.reading),0) AS total
FROM readings r JOIN stations s ON r.zone=s.zone
GROUP BY s.station_id
) query_result
ORDER BY CASE WHEN station_id IS NULL THEN 1 ELSE 0 END ASC, station_id ASC;PostgreSQL
SELECT station_id AS station_id, total AS total
FROM (
SELECT s.station_id AS station_id, COALESCE(SUM(r.reading),0) AS total
FROM readings r JOIN stations s ON r.zone=s.zone
GROUP BY s.station_id
) query_result
ORDER BY CASE WHEN station_id IS NULL THEN 1 ELSE 0 END ASC, station_id ASC;MySQL
SELECT station_id AS station_id, total AS total
FROM (
SELECT s.station_id AS station_id, COALESCE(SUM(r.reading),0) AS total
FROM readings r JOIN stations s ON r.zone=s.zone
GROUP BY s.station_id
) query_result
ORDER BY CASE WHEN station_id IS NULL THEN 1 ELSE 0 END ASC, station_id ASC;預期查詢結果
station_id | total 2 | 14 3 | 7 5 | 14 8 | 5 9 | 3 14 | -4
06｜主鍵、外鍵與正規化
本機範例把裝置與測量分成兩表，以 device_id 參照，避免每筆讀值重複保存裝置屬性。每裝置可有多筆測量，外鍵不是一對一保證。外鍵限制參照必須存在；SQLite 需在交易開始前為連線開啟 PRAGMA foreign_keys=ON，MySQL 使用 InnoDB。刪除被參照裝置前，須明確選擇限制或級聯策略，本例不啟用級聯。
SQLite
PRAGMA foreign_keys=ON;
CREATE TABLE scratch_devices (device_id INTEGER NOT NULL PRIMARY KEY,zone VARCHAR(20) NOT NULL);
CREATE TABLE scratch_measurements (id INTEGER NOT NULL PRIMARY KEY,device_id INTEGER NOT NULL,reading INTEGER,FOREIGN KEY(device_id) REFERENCES scratch_devices(device_id));
INSERT INTO scratch_devices(device_id,zone) VALUES (1,'north');
INSERT INTO scratch_measurements(id,device_id,reading) VALUES (1,1,3),(2,1,7);
SELECT m.id AS id,d.zone AS zone,m.reading AS reading FROM scratch_measurements m JOIN scratch_devices d ON m.device_id=d.device_id ORDER BY m.id;PostgreSQL
CREATE TABLE scratch_devices (device_id INTEGER NOT NULL PRIMARY KEY,zone VARCHAR(20) NOT NULL);
CREATE TABLE scratch_measurements (id INTEGER NOT NULL PRIMARY KEY,device_id INTEGER NOT NULL,reading INTEGER,FOREIGN KEY(device_id) REFERENCES scratch_devices(device_id));
INSERT INTO scratch_devices(device_id,zone) VALUES (1,'north');
INSERT INTO scratch_measurements(id,device_id,reading) VALUES (1,1,3),(2,1,7);
SELECT m.id AS id,d.zone AS zone,m.reading AS reading FROM scratch_measurements m JOIN scratch_devices d ON m.device_id=d.device_id ORDER BY m.id;MySQL
CREATE TABLE scratch_devices (device_id INTEGER NOT NULL PRIMARY KEY,zone VARCHAR(20) NOT NULL) ENGINE=InnoDB;
CREATE TABLE scratch_measurements (id INTEGER NOT NULL PRIMARY KEY,device_id INTEGER NOT NULL,reading INTEGER,FOREIGN KEY(device_id) REFERENCES scratch_devices(device_id)) ENGINE=InnoDB;
INSERT INTO scratch_devices(device_id,zone) VALUES (1,'north');
INSERT INTO scratch_measurements(id,device_id,reading) VALUES (1,1,3),(2,1,7);
SELECT m.id AS id,d.zone AS zone,m.reading AS reading FROM scratch_measurements m JOIN scratch_devices d ON m.device_id=d.device_id ORDER BY m.id;預期查詢結果
id | zone | reading 1 | "north" | 3 2 | "north" | 7
WHEN SOMETHING GOES WRONG
遇到錯誤，先檢查這裡
連接條件漏掉或寫錯
先核對一對來源列為何匹配，再對照全體配對數。
以 DISTINCT 去掉合法配對
不同 station_id 是不同配對，即使 reading 相同仍需保留。
把配對數當原紀錄數
先說明統計粒度是配對、測站或 reading id。
PAUSE AND TRY
先不要急著看別人的寫法
- 將同區域測站從一個增為兩個，哪些讀值會複製成多個配對？
- 兩側 zone 都為 NULL 時，為何 INNER JOIN 不配對？
- 比較 INNER JOIN 與 CROSS JOIN 在空右表時的列數。
先改一列資料，預測查詢結果，再切換引擎確認語法與排序。
把這幾件事帶走
- 明確寫出 ON 匹配條件
- 保留一對多的所有配對
- 在連接後依正確粒度統計
FROM READING TO PRACTICE
用三道免費題，練習本章觀念
從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。
延伸閱讀：資料庫官方文件
需要查閱更完整的規則時，可參考以下官方文件。