SQL 子查詢與 EXISTS

先預測結果，再看輸出。

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

01｜先理解資料與結果

純量子查詢提供一個值，例如全體 AVG(reading)。EXISTS 只看是否至少有一列符合條件，因此多個測站符合時也不會複製原始 reading。相關子查詢引用外層 r.zone，邏輯上為每原列判斷其對應資料，實際最佳化器可能改寫。

02｜建立正確查詢規則

NOT IN 並不是任何情況都等於 NOT EXISTS。集合中存在 NULL 時，未命中值也可能得到 UNKNOWN。空子查詢的 NOT IN 則為真，包含外層值 NULL。若只比較已知目標，明寫 WHERE target IS NOT NULL；若需要缺值安全相等，使用 a=b OR(a IS NULL AND b IS NULL)。

簡單範例｜高於全體平均

只保留reading 大於全體非 NULL reading 平均；沒有有效平均時不輸出的原紀錄，輸出 id、zone、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 id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE r.reading>(SELECT AVG(reading) FROM readings)
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE r.reading>(SELECT AVG(reading) FROM readings)
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE r.reading>(SELECT AVG(reading) FROM readings)
) query_result
ORDER BY id ASC;

預期查詢結果

id | zone | reading
3 | "north" | 5
4 | "west" | 5
8 | "north" | 9
9 | "" | 3
11 | "south" | 9

中等範例｜有站測量

只保留存在同 zone 測站，NULL zone 不匹配；每原紀錄只輸出一次的原紀錄，輸出 id、zone、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 id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE EXISTS (SELECT 1 FROM stations s WHERE s.zone=r.zone)
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE EXISTS (SELECT 1 FROM stations s WHERE s.zone=r.zone)
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE EXISTS (SELECT 1 FROM stations s WHERE s.zone=r.zone)
) query_result
ORDER BY id ASC;

預期查詢結果

id | zone | reading
2 | "north" | 0
3 | "north" | 5
4 | "west" | 5
5 | "east" | -5
7 | "south" | -2
8 | "north" | 9
9 | "" | 3
10 | "west" | 0
11 | "south" | 9
12 | "east" | 1

困難範例｜有效目標集合外

只保留先移除 NULL target 再 NOT IN；非空有效集合排除 NULL reading，空有效集合保留所有原紀錄的原紀錄，輸出 id、zone、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 id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE r.reading NOT IN (SELECT target FROM stations WHERE target IS NOT NULL)
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE r.reading NOT IN (SELECT target FROM stations WHERE target IS NOT NULL)
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT r.id AS id, r.zone AS zone, r.reading AS reading
FROM readings r
WHERE r.reading NOT IN (SELECT target FROM stations WHERE target IS NOT NULL)
) query_result
ORDER BY id ASC;

預期查詢結果

id | zone | reading
5 | "east" | -5

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

子查詢回傳多列卻當單值

先聚合或限定唯一列，再放入單值比較。

NOT IN 未排除集合 NULL

按題意處理缺值，不能只看正常目標測資。

相關條件忘記外層別名

檢查 q.zone=r.zone 兩側是否分別指向內外查詢。

PAUSE AND TRY

先不要急著看別人的寫法

目標表只有一個 NULL 時，reading=7 的 NOT IN 為何不保留？
目標表為空時，reading=NULL 的 NOT IN 會如何？
同區域有兩個測站，EXISTS 與 JOIN 的列數為何不同？

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

把這幾件事帶走

分辨純量與存在性子查詢
建立相關子查詢
明確處理 NOT IN 的 NULL 與空集合

FROM READING TO PRACTICE

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

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

簡單

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

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

01｜先理解資料與結果

02｜建立正確查詢規則

簡單範例｜高於全體平均

中等範例｜有站測量

困難範例｜有效目標集合外

遇到錯誤，先檢查這裡

先不要急著看別人的寫法

把這幾件事帶走

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

201. 高於全體平均

209. 有站測量

218. 有效目標集合外

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