Skip to content

Data Science › Databases › SQL 查詢基礎

SQL 查詢基礎 (SQL Query Foundations) ​

跨資料庫通用的查詢心智模型:子句的執行順序、過濾列與過濾群組的差別、三種 COUNT,以及 NULL 造成的意外。方言差異(TOP / LIMIT、日期函式)見 SQL 方言速查。

執行順序 ​

SQL 寫的順序不等於邏輯處理順序。多數語法錯誤(alias 不能用、聚合不能放 WHERE)都來自這裡。

邏輯順序子句作用這一步能用 SELECT alias?
1FROM / JOIN決定資料來源、合併表否
2WHERE過濾個別列否
3GROUP BY分組多數否(PostgreSQL、MySQL、DuckDB 允許)
4HAVING過濾群組多數否
5SELECT計算欄位、命名 alias;window function 在這裡算—
6DISTINCT去重—
7ORDER BY排序是
8LIMIT / OFFSET / FETCH截取—
sql
-- 錯:WHERE 在 SELECT 之前,alias 還不存在
SELECT budget / 1e6 AS budget_m FROM films WHERE budget_m > 100;

-- 對 1:在 WHERE 重寫運算式
SELECT budget / 1e6 AS budget_m FROM films WHERE budget / 1e6 > 100;

-- 對 2:先包成子查詢或 CTE,外層再過濾
WITH f AS (SELECT title, budget / 1e6 AS budget_m FROM films)
SELECT * FROM f WHERE budget_m > 100;

完整骨架 ​

sql
SELECT
  certification,
  COUNT(*)                 AS n_films,
  ROUND(AVG(duration), 1)  AS avg_duration
FROM films
WHERE release_year >= 2000                 -- 1. 先過濾列
  AND certification IN ('G', 'PG', 'PG-13')
GROUP BY certification                     -- 2. 分組
HAVING COUNT(*) > 500                      -- 3. 過濾群組(聚合條件只能放這裡)
ORDER BY n_films DESC                      -- 4. 排序(可用 alias)
LIMIT 3;                                   -- 5. 取前 3;SQL Server 用 SELECT TOP (3)
子句規則
GROUP BYSELECT 裡非聚合的欄位都必須出現在 GROUP BY(MySQL 舊模式不檢查,會回傳任意值)
HAVING vs WHEREWHERE 過濾列(「2000 年的電影」);HAVING 過濾群組(「平均片長 > 120 分鐘的年份」)
ORDER BY多欄排序時第二欄是同分的 tie-breaker;每欄可各自 ASC / DESC
LIMIT沒有 ORDER BY 時回傳哪幾列沒有保證,只適合預覽;top-N 一定要排序
DISTINCT a, b對欄位組合去重,不是各欄分別去重

過濾條件 ​

運算子例子注意
比較release_year > 1960
BETWEENrelease_year BETWEEN 1994 AND 2000含頭含尾;時間戳用 >= '2024-01-01' AND < '2024-02-01' 才不會漏掉月底當天的時間
INcertification IN ('G', 'PG')比多個 OR 清楚
LIKEname LIKE 'Ade%'、'Ev_'% 任意長度、_ 單一字元;開頭是 % 無法用一般 index
IS NULLbudget IS NULL不能寫 = NULL
AND / ORa = 1 AND (b = 2 OR c = 3)AND 優先於 OR,混用時一定加括號

聚合與 COUNT ​

sql
SELECT
  COUNT(*)                 AS n_rows,          -- 列數,不管 NULL
  COUNT(birthdate)         AS n_known,         -- 該欄非 NULL 的列數
  COUNT(DISTINCT birthdate) AS n_unique,       -- 不重複的非 NULL 值
  AVG(budget), SUM(budget), MIN(budget), MAX(budget),
  ROUND(AVG(budget), 2)    AS avg_budget_2dp
FROM people;
函式對 NULL 的處理
COUNT(*)計入
COUNT(col)、SUM、AVG、MIN、MAX忽略;AVG 的分母是非 NULL 的列數
全部是 NULL 或沒有列SUM / AVG 回傳 NULL(不是 0);COUNT 回傳 0

COUNT(*) - COUNT(col) 就是該欄缺值數,是資料完整性檢查的第一步。

NULL:三值邏輯 ​

NULL 表示未知,不是 0 也不是空字串。任何與 NULL 的比較結果都是 UNKNOWN,而 WHERE 只保留 TRUE 的列。

運算式結果
NULL = NULLUNKNOWN(所以要用 IS NULL)
NULL <> 'x'UNKNOWN
TRUE AND NULL / FALSE AND NULLUNKNOWN / FALSE
TRUE OR NULL / FALSE OR NULLTRUE / UNKNOWN
NULL + 1、'a' || NULLNULL(SQL Server + 串接也是;Oracle 例外,|| 把 NULL 當空字串)
陷阱例子改法
<> 會排除 NULLWHERE status <> 'done' 不回傳 status IS NULL 的列WHERE status <> 'done' OR status IS NULL;PostgreSQL / DuckDB 可用 status IS DISTINCT FROM 'done'
NOT IN 子查詢含 NULLWHERE id NOT IN (SELECT id FROM b),b.id 只要有一個 NULL,結果就是空的改用 NOT EXISTS(見 anti join)
平均值偏誤AVG(score) 只平均有值的列先確認缺值比例與缺失機制(見 缺失機制)
填補COALESCE(x, 0)只在 NULL 確實代表 0 時才用

SQL ↔ pandas ↔ dplyr 對照 ​

SQLpandasdplyr
SELECT a, bdf[["a", "b"]]select(a, b)
WHERE x > 1df[df.x > 1] / df.query("x > 1")filter(x > 1)
SELECT a * 2 AS a2df.assign(a2=df.a * 2)mutate(a2 = a * 2)
GROUP BY g + 聚合df.groupby("g").agg(n=("x", "size"))group_by(g) |> summarise(n = n())
HAVING COUNT(*) > 5聚合後再篩選filter(n > 5)(在 summarise 之後)
ORDER BY x DESC LIMIT 5df.nlargest(5, "x")slice_max(x, n = 5)
COUNT(DISTINCT x)df.x.nunique()n_distinct(x)
x IS NULLdf.x.isna()is.na(x)

差別在 NULL:pandas 的 groupby 預設丟掉 key 為 NaN 的群組(dropna=False 保留),SQL 與 dplyr 則把 NULL / NA 當成一組。

pandas 細節見 groupby;dplyr 見 Tidyverse 工作流。