SQL 外部連接

先預測結果，再看輸出。

SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。

01｜先理解資料與結果

LEFT JOIN 先找匹配，左列完全沒有匹配時產生一列右側全 NULL 的結果。COUNT(*) 會把這個補列算一列，因此計真匹配數應用 COUNT(右側非 NULL id)。若有匹配但其 reading 缺值，COUNT(reading) 仍為 0；這與「完全沒匹配」不是同一件事。

02｜建立正確查詢規則

ON r.reading>0 只限制哪些右列能匹配，仍保留沒有正值的左項目。WHERE r.reading>0 則在補 NULL 後篩結果，會排除未匹配列。找真正未匹配項目時，用右側不可能缺值的 id IS NULL，而非可缺值的 reading IS NULL。

簡單範例｜測站完整配對

以 zone 相等配對，NULL zone 不匹配，一對多逐配對處理。逐配對輸出所有測站；沒有匹配的站仍輸出一列，reading_id、reading 為 NULL。

先閱讀下方 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, reading_id AS reading_id, reading AS reading
FROM (
SELECT s.station_id AS station_id, r.id AS reading_id, r.reading AS reading
FROM stations s LEFT JOIN readings r ON r.zone=s.zone
) query_result
ORDER BY CASE WHEN station_id IS NULL THEN 1 ELSE 0 END ASC, station_id ASC, CASE WHEN reading_id IS NULL THEN 1 ELSE 0 END ASC, reading_id ASC;

PostgreSQL

SELECT station_id AS station_id, reading_id AS reading_id, reading AS reading
FROM (
SELECT s.station_id AS station_id, r.id AS reading_id, r.reading AS reading
FROM stations s LEFT JOIN readings r ON r.zone=s.zone
) query_result
ORDER BY CASE WHEN station_id IS NULL THEN 1 ELSE 0 END ASC, station_id ASC, CASE WHEN reading_id IS NULL THEN 1 ELSE 0 END ASC, reading_id ASC;

MySQL

SELECT station_id AS station_id, reading_id AS reading_id, reading AS reading
FROM (
SELECT s.station_id AS station_id, r.id AS reading_id, r.reading AS reading
FROM stations s LEFT JOIN readings r ON r.zone=s.zone
) query_result
ORDER BY CASE WHEN station_id IS NULL THEN 1 ELSE 0 END ASC, station_id ASC, CASE WHEN reading_id IS NULL THEN 1 ELSE 0 END ASC, reading_id ASC;

預期查詢結果

station_id | reading_id | reading
2 | 2 | 0
2 | 3 | 5
2 | 8 | 9
3 | 7 | -2
3 | 11 | 9
5 | 2 | 0
5 | 3 | 5
5 | 8 | 9
8 | 4 | 5
8 | 10 | 0
9 | 9 | 3
11 | NULL | NULL
14 | 5 | -5
14 | 12 | 1

中等範例｜測站完整高點

以 zone 相等配對，NULL zone 不匹配，一對多逐配對處理。每測站最高有效 reading；無有效值為 NULL。

先閱讀下方 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, highest AS highest
FROM (
SELECT s.station_id AS station_id, MAX(r.reading) AS highest
FROM stations s LEFT JOIN readings r 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, highest AS highest
FROM (
SELECT s.station_id AS station_id, MAX(r.reading) AS highest
FROM stations s LEFT JOIN readings r 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, highest AS highest
FROM (
SELECT s.station_id AS station_id, MAX(r.reading) AS highest
FROM stations s LEFT JOIN readings r 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 | highest
2 | 9
3 | 9
5 | 9
8 | 5
9 | 3
11 | NULL
14 | 1

困難範例｜啟用目標數

以 zone 相等配對，NULL zone 不匹配，一對多逐配對處理。配對條件要求 enabled=1；每原紀錄計算此測站數，無則 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 reading_id AS reading_id, match_count AS match_count
FROM (
SELECT r.id AS reading_id, COUNT(s.station_id) AS match_count
FROM readings r LEFT JOIN stations s ON r.zone=s.zone AND s.enabled=1
GROUP BY r.id
) query_result
ORDER BY CASE WHEN reading_id IS NULL THEN 1 ELSE 0 END ASC, reading_id ASC;

PostgreSQL

SELECT reading_id AS reading_id, match_count AS match_count
FROM (
SELECT r.id AS reading_id, COUNT(s.station_id) AS match_count
FROM readings r LEFT JOIN stations s ON r.zone=s.zone AND s.enabled=1
GROUP BY r.id
) query_result
ORDER BY CASE WHEN reading_id IS NULL THEN 1 ELSE 0 END ASC, reading_id ASC;

MySQL

SELECT reading_id AS reading_id, match_count AS match_count
FROM (
SELECT r.id AS reading_id, COUNT(s.station_id) AS match_count
FROM readings r LEFT JOIN stations s ON r.zone=s.zone AND s.enabled=1
GROUP BY r.id
) query_result
ORDER BY CASE WHEN reading_id IS NULL THEN 1 ELSE 0 END ASC, reading_id ASC;

預期查詢結果

reading_id | match_count
1 | 0
2 | 2
3 | 2
4 | 0
5 | 0
6 | 0
7 | 0
8 | 2
9 | 1
10 | 0
11 | 0
12 | 0

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

COUNT(*) 把未匹配算一筆

用 COUNT(r.id) 等非 NULL 匹配鍵。

在 WHERE 無意間消除外部連接

若要求保留每測站，右側資格限制通常需放 ON。

用可缺值欄位判斷有無匹配

使用右側唯一非 NULL id，避免把缺測紀錄當成未配對。

PAUSE AND TRY

先不要急著看別人的寫法

只有一個未匹配測站時，COUNT(*) 與 COUNT(r.id) 各為何？
為同區域新增一筆 reading=NULL，匹配數與有效值數各如何變化？
把 reading>0 從 ON 移到 WHERE，哪些測站消失？

先改一列資料，預測查詢結果，再切換引擎確認語法與排序。

把這幾件事帶走

保留左側無匹配列
正確計算實際匹配數
分辨 ON 篩配對與 WHERE 篩結果

FROM READING TO PRACTICE

用三道免費題，練習本章觀念

從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。

簡單

延伸閱讀：資料庫官方文件

需要查閱更完整的規則時，可參考以下官方文件。

01｜先理解資料與結果

02｜建立正確查詢規則

簡單範例｜測站完整配對

中等範例｜測站完整高點

困難範例｜啟用目標數

遇到錯誤，先檢查這裡

先不要急著看別人的寫法

把這幾件事帶走

用三道免費題，練習本章觀念

176. 測站完整配對

184. 測站完整高點

193. 啟用目標數

延伸閱讀：資料庫官方文件