SQL 集合運算

先預測結果，再看輸出。

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

01｜先理解資料與結果

UNION 合併後去重，UNION ALL 保留每次出現。集合運算要求左右查詢欄數相同、位置型別能相容；輸出欄名通常來自左查詢，所以本課仍明確指定外層別名。去重時兩個 NULL 視為同值，與 WHERE 中 NULL=NULL 不為真不同。

02｜建立正確查詢規則

交集保留兩側都有的完整元素，差集保留一側有、另一側沒有的元素。為涵蓋不同 MySQL 版本，官方解可用 DISTINCT 與 EXISTS／NOT EXISTS 實現，必須寫缺值安全相等。若題目要求多重集合次數，先計兩側頻率：共有次數為 min(left_count,right_count)，左側剩餘為 max(left_count-right_count,0)。

簡單範例｜合併讀值種類

對 left_samples 與 right_samples 的 reading 做聯集，重複完整欄位組合只保留一次。NULL 在此集合比對中視為相等。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "left_samples", "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": [[3, "north", 5, 2, 1, 2, "alpha"], [4, "west", 5, -2, 1, 2, "ab"], [11, "south", 9, 2, 1, 20, "atlas"], [9, "", 3, 3, 1, 8, ""], [8, "north", 9, 1, null, 8, "omega"], [7, "south", -2, 0, 0, 4, "a"], [12, "east", 1, 0, null, 30, "beta"], [1, null, null, null, null, 1, null], [6, null, null, 4, 1, 4, "gamma"], [5, "east", -5, 5, 0, 3, "beta"], [10, "west", 0, null, 0, 15, "delta"], [2, "north", 0, 0, 0, 1, ""]]}, {"name": "right_samples", "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": [[9, "", 3, 3, 1, 8, ""], [10, "west", 0, null, 0, 15, "delta"], [12, "east", 1, 0, null, 30, "beta"], [7, "south", -2, 0, 0, 4, "a"], [21, "north", 5, 3, 1, 10, "a"], [3, "north", 5, 2, 1, 2, "alpha"], [8, "north", 9, 1, null, 8, "omega"], [5, "east", -5, 5, 0, 3, "beta"], [1, null, null, null, null, 1, null], [2, "north", 0, 0, 0, 1, ""], [20, "north", 5, 3, 1, 10, "a"]]}]}

SQLite

SELECT reading AS reading
FROM (
SELECT reading AS reading
FROM (SELECT reading FROM left_samples UNION SELECT reading FROM right_samples) merged_values
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

PostgreSQL

SELECT reading AS reading
FROM (
SELECT reading AS reading
FROM (SELECT reading FROM left_samples UNION SELECT reading FROM right_samples) merged_values
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

MySQL

SELECT reading AS reading
FROM (
SELECT reading AS reading
FROM (SELECT reading FROM left_samples UNION SELECT reading FROM right_samples) merged_values
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

預期查詢結果

reading
-5
-2
0
1
3
5
9
NULL

中等範例｜合併區域讀值

對 left_samples 與 right_samples 的 zone, reading 做聯集，重複完整欄位組合只保留一次。NULL 在此集合比對中視為相等。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "left_samples", "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": [[3, "north", 5, 2, 1, 2, "alpha"], [4, "west", 5, -2, 1, 2, "ab"], [11, "south", 9, 2, 1, 20, "atlas"], [9, "", 3, 3, 1, 8, ""], [8, "north", 9, 1, null, 8, "omega"], [7, "south", -2, 0, 0, 4, "a"], [12, "east", 1, 0, null, 30, "beta"], [1, null, null, null, null, 1, null], [6, null, null, 4, 1, 4, "gamma"], [5, "east", -5, 5, 0, 3, "beta"], [10, "west", 0, null, 0, 15, "delta"], [2, "north", 0, 0, 0, 1, ""]]}, {"name": "right_samples", "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": [[9, "", 3, 3, 1, 8, ""], [10, "west", 0, null, 0, 15, "delta"], [12, "east", 1, 0, null, 30, "beta"], [7, "south", -2, 0, 0, 4, "a"], [21, "north", 5, 3, 1, 10, "a"], [3, "north", 5, 2, 1, 2, "alpha"], [8, "north", 9, 1, null, 8, "omega"], [5, "east", -5, 5, 0, 3, "beta"], [1, null, null, null, null, 1, null], [2, "north", 0, 0, 0, 1, ""], [20, "north", 5, 3, 1, 10, "a"]]}]}

SQLite

SELECT zone AS zone, reading AS reading
FROM (
SELECT zone AS zone, reading AS reading
FROM (SELECT zone, reading FROM left_samples UNION SELECT zone, reading FROM right_samples) merged_values
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE BINARY ASC, CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

PostgreSQL

SELECT zone AS zone, reading AS reading
FROM (
SELECT zone AS zone, reading AS reading
FROM (SELECT zone, reading FROM left_samples UNION SELECT zone, reading FROM right_samples) merged_values
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE "C" ASC, CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

MySQL

SELECT zone AS zone, reading AS reading
FROM (
SELECT zone AS zone, reading AS reading
FROM (SELECT zone, reading FROM left_samples UNION SELECT zone, reading FROM right_samples) merged_values
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE utf8mb4_bin ASC, CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

預期查詢結果

zone | reading
"" | 3
"east" | -5
"east" | 1
"north" | 0
"north" | 5
"north" | 9
"south" | -2
"south" | 9
"west" | 0
"west" | 5
NULL | NULL

困難範例｜合併值頻率

按 reading 分組計算兩表所有原始列的出現次數，NULL 同組。 NULL 在計次與比對時歸為同值。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "left_samples", "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": [[3, "north", 5, 2, 1, 2, "alpha"], [4, "west", 5, -2, 1, 2, "ab"], [11, "south", 9, 2, 1, 20, "atlas"], [9, "", 3, 3, 1, 8, ""], [8, "north", 9, 1, null, 8, "omega"], [7, "south", -2, 0, 0, 4, "a"], [12, "east", 1, 0, null, 30, "beta"], [1, null, null, null, null, 1, null], [6, null, null, 4, 1, 4, "gamma"], [5, "east", -5, 5, 0, 3, "beta"], [10, "west", 0, null, 0, 15, "delta"], [2, "north", 0, 0, 0, 1, ""]]}, {"name": "right_samples", "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": [[9, "", 3, 3, 1, 8, ""], [10, "west", 0, null, 0, 15, "delta"], [12, "east", 1, 0, null, 30, "beta"], [7, "south", -2, 0, 0, 4, "a"], [21, "north", 5, 3, 1, 10, "a"], [3, "north", 5, 2, 1, 2, "alpha"], [8, "north", 9, 1, null, 8, "omega"], [5, "east", -5, 5, 0, 3, "beta"], [1, null, null, null, null, 1, null], [2, "north", 0, 0, 0, 1, ""], [20, "north", 5, 3, 1, 10, "a"]]}]}

SQLite

SELECT reading AS reading, occurrences AS occurrences
FROM (
SELECT reading AS reading, COUNT(*) AS occurrences
FROM (SELECT reading FROM left_samples UNION ALL SELECT reading FROM right_samples) combined_values
GROUP BY reading
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

PostgreSQL

SELECT reading AS reading, occurrences AS occurrences
FROM (
SELECT reading AS reading, COUNT(*) AS occurrences
FROM (SELECT reading FROM left_samples UNION ALL SELECT reading FROM right_samples) combined_values
GROUP BY reading
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

MySQL

SELECT reading AS reading, occurrences AS occurrences
FROM (
SELECT reading AS reading, COUNT(*) AS occurrences
FROM (SELECT reading FROM left_samples UNION ALL SELECT reading FROM right_samples) combined_values
GROUP BY reading
) query_result
ORDER BY CASE WHEN reading IS NULL THEN 1 ELSE 0 END ASC, reading ASC;

預期查詢結果

reading | occurrences
-5 | 2
-2 | 2
0 | 4
1 | 2
3 | 2
5 | 5
9 | 3
NULL | 3

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

UNION ALL 與 UNION 混用

先判斷需要每次出現還是不同值，兩者不可互換。

只比較組合中的部分欄位

zone,reading 組合需兩欄都相等，包括對應 NULL。

先去重後想計原次數

次數需要保留原始列或先分別計數。

PAUSE AND TRY

先不要急著看別人的寫法

左側 [5,5,NULL]、右側 [5,NULL,NULL]，聯集與串接各有幾列？
同 reading 不同 zone 是否為相同完整元素？
上述兩表的 reading=5 共有次數與左剩餘各為何？

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

把這幾件事帶走

比較 UNION 與 UNION ALL
理解交集、差集與 NULL 安全比對
以次數模型處理重複值

FROM READING TO PRACTICE

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

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

簡單

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

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

01｜先理解資料與結果

02｜建立正確查詢規則

簡單範例｜合併讀值種類

中等範例｜合併區域讀值

困難範例｜合併值頻率

遇到錯誤，先檢查這裡

先不要急著看別人的寫法

把這幾件事帶走

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

226. 合併讀值種類

234. 合併區域讀值

243. 合併值頻率

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