先預測結果，再看輸出。
SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。
01｜先理解資料與結果
WHERE 在原始列上判斷條件，只保留結果為 TRUE 的列。涉及 NULL 的一般比較通常得到 UNKNOWN，例如 NULL=0 與 NULL=NULL 都不是 TRUE。用 IS NULL／IS NOT NULL 問值是否缺漏。空字串是存在的文字，0 是存在的數值，兩者都不是 NULL。
02｜建立正確查詢規則
AND 需要兩個條件都為真；OR 只要任一條件為真即可保留，因此 NULL reading 搭配 flag=1 仍可通過 reading>0 OR flag=1。NOT UNKNOWN 仍是 UNKNOWN，故 NOT(reading>0) 不會保留 NULL reading。BETWEEN 包含上下端點；LIKE 的 % 表示任意長度字串，_ 表示一個字元。
簡單範例｜有效讀值
只保留reading 不是 NULL的原始紀錄，輸出 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"]]}]}SQLite
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE reading IS NOT NULL
) query_result
ORDER BY id ASC;PostgreSQL
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE reading IS NOT NULL
) query_result
ORDER BY id ASC;MySQL
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE reading IS NOT NULL
) 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
中等範例｜已標記測量
只保留flag 等於 1的原始紀錄，輸出 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"]]}]}SQLite
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE flag=1
) query_result
ORDER BY id ASC;PostgreSQL
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE flag=1
) query_result
ORDER BY id ASC;MySQL
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE flag=1
) query_result
ORDER BY id ASC;預期查詢結果
id | zone | reading 3 | "north" | 5 4 | "west" | 5 6 | NULL | NULL 9 | "" | 3 11 | "south" | 9
困難範例｜雙欄缺漏
只保留reading 與 weight 都是 NULL的原始紀錄，輸出 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"]]}]}SQLite
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE reading IS NULL AND weight IS NULL
) query_result
ORDER BY id ASC;PostgreSQL
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE reading IS NULL AND weight IS NULL
) query_result
ORDER BY id ASC;MySQL
SELECT id AS id, zone AS zone, reading AS reading
FROM (
SELECT id AS id, zone AS zone, reading AS reading
FROM readings
WHERE reading IS NULL AND weight IS NULL
) query_result
ORDER BY id ASC;預期查詢結果
id | zone | reading 1 | NULL | NULL
WHEN SOMETHING GOES WRONG
遇到錯誤，先檢查這裡
用 = NULL 找缺值
改用 IS NULL；= 是一般比較，不是缺值判斷。
忘記 AND／OR 的括號
AND 優先於 OR；用括號明確寫出預期的條件組合。
把 UNKNOWN 當成 FALSE 取反
先列出缺值情況，再決定是否額外 OR reading IS NULL。
PAUSE AND TRY
先不要急著看別人的寫法
- 對 TRUE OR UNKNOWN 與 FALSE OR UNKNOWN 分別預測是否保留。
- reading 為 5、-2、NULL 時，BETWEEN -2 AND 5 各如何處理？
- 若要保留缺測或非正讀值，NOT(reading>0) 還缺哪個條件？
先改一列資料，預測查詢結果，再切換引擎確認語法與排序。
把這幾件事帶走
- 組合比較、AND、OR、NOT
- 以 IS NULL 檢查缺值
- 說明為何 NOT 不會把未知條件變成真
FROM READING TO PRACTICE
用三道免費題，練習本章觀念
從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。
延伸閱讀：資料庫官方文件
需要查閱更完整的規則時，可參考以下官方文件。