SELECT 深入解析

SELECT 是 SQL 中最常用的語句。本篇將深入探討查詢的各個面向，從基本語法到進階技巧。

---

一、 基本語法結構

SELECT [DISTINCT] 欄位列表
FROM 資料表
[WHERE 條件]
[GROUP BY 分組欄位]
[HAVING 分組條件]
[ORDER BY 排序欄位]
[LIMIT 數量 OFFSET 偏移]

執行順序（與書寫順序不同）：

理解執行順序很重要——例如，你不能在 WHERE 中使用 SELECT 定義的別名。

---

二、 WHERE 條件

比較運算子

SELECT * FROM products WHERE price = 100;
SELECT * FROM products WHERE price <> 100;  -- 不等於
SELECT * FROM products WHERE price != 100;  -- 同上
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price <= 100;

邏輯運算子

-- AND：兩個條件都要滿足
SELECT * FROM products WHERE price > 100 AND stock > 0;

-- OR：至少一個條件滿足
SELECT * FROM products WHERE category = 'book' OR category = 'magazine';

-- NOT：反轉條件
SELECT * FROM products WHERE NOT is_deleted;

-- 複合條件（用括號明確優先順序）
SELECT * FROM products
WHERE (category = 'book' OR category = 'magazine')
  AND price < 500;

BETWEEN

-- 範圍查詢（包含邊界）
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- 等同於
SELECT * FROM products WHERE price >= 100 AND price <= 500;

-- 日期範圍
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

IN

-- 多值匹配
SELECT * FROM products WHERE category IN ('book', 'magazine', 'newspaper');

-- 等同於
SELECT * FROM products
WHERE category = 'book'
   OR category = 'magazine'
   OR category = 'newspaper';

-- 搭配子查詢
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE is_active);

LIKE 與模式匹配

-- % 匹配任意字元（0 個或多個）
SELECT * FROM products WHERE name LIKE '%apple%';    -- 包含 apple
SELECT * FROM products WHERE name LIKE 'apple%';     -- 以 apple 開頭
SELECT * FROM products WHERE name LIKE '%apple';     -- 以 apple 結尾

-- _ 匹配單一字元
SELECT * FROM products WHERE code LIKE 'A_B';        -- A?B

-- ILIKE：不區分大小寫
SELECT * FROM products WHERE name ILIKE '%apple%';

正則表達式

-- ~ 區分大小寫
SELECT * FROM products WHERE name ~ '^[A-Z]';

-- ~* 不區分大小寫
SELECT * FROM products WHERE email ~* '^admin@';

-- !~ 不匹配
SELECT * FROM products WHERE name !~ '[0-9]';

---

三、 ORDER BY 排序

-- 升序（預設）
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY price;  -- 同上

-- 降序
SELECT * FROM products ORDER BY price DESC;

-- 多欄位排序
SELECT * FROM products ORDER BY category ASC, price DESC;

-- 使用欄位位置（不推薦，可讀性差）
SELECT name, price FROM products ORDER BY 2 DESC;

-- NULL 值排序
SELECT * FROM products ORDER BY discount NULLS FIRST;
SELECT * FROM products ORDER BY discount NULLS LAST;

---

四、 LIMIT 與 OFFSET

-- 取前 10 筆
SELECT * FROM products ORDER BY created_at DESC LIMIT 10;

-- 分頁：跳過前 20 筆，取 10 筆
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- 效能問題：OFFSET 大時效率很差
-- 替代方案：使用 Keyset Pagination
SELECT * FROM products
WHERE id > 12345  -- 上一頁最後一筆的 ID
ORDER BY id
LIMIT 10;

> **避免大 OFFSET** > `OFFSET 100000` 會讓資料庫先掃描 100000 筆再丟棄，效能極差。改用 Keyset Pagination。

---

五、 DISTINCT 去重

-- 去除重複的類別
SELECT DISTINCT category FROM products;

-- 多欄位去重
SELECT DISTINCT category, brand FROM products;

-- DISTINCT ON（PostgreSQL 專有）：每組取第一筆
SELECT DISTINCT ON (category) category, name, price
FROM products
ORDER BY category, price DESC;
-- 結果：每個類別中價格最高的產品

---

六、 GROUP BY 分組

-- 計算每個類別的產品數量
SELECT category, COUNT(*) as product_count
FROM products
GROUP BY category;

-- 多欄位分組
SELECT category, brand, COUNT(*)
FROM products
GROUP BY category, brand;

-- 常用聚合函數
SELECT
    category,
    COUNT(*) as total,
    SUM(price) as total_price,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category;

GROUP BY 規則

> **SELECT 中的欄位必須**：

1. 出現在 GROUP BY 中，或
2. 被聚合函數包裝

-- 錯誤：name 沒有分組也沒有聚合
SELECT category, name, COUNT(*) FROM products GROUP BY category;

-- 正確
SELECT category, COUNT(*) FROM products GROUP BY category;
SELECT category, MAX(name) FROM products GROUP BY category;

---

七、 HAVING 過濾分組

HAVING 是針對分組後的結果進行過濾，與 WHERE 的差異：

WHERE	HAVING
在分組前過濾	在分組後過濾
不能用聚合函數	可以用聚合函數

-- 找出產品數量超過 10 的類別
SELECT category, COUNT(*) as cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- WHERE 與 HAVING 並用
SELECT category, COUNT(*) as cnt
FROM products
WHERE is_active = true        -- 先過濾：只算啟用的產品
GROUP BY category
HAVING COUNT(*) > 10;         -- 再過濾：只顯示超過 10 個的類別

---

八、 NULL 的處理

NULL 代表「未知」，不是「空值」或「零」。

NULL 的比較

-- 錯誤：這永遠不會匹配
SELECT * FROM users WHERE phone = NULL;

-- 正確：使用 IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

NULL 與運算

-- NULL 參與運算，結果都是 NULL
SELECT 10 + NULL;        -- NULL
SELECT 'hello' || NULL;  -- NULL
SELECT NULL = NULL;      -- NULL（不是 true！）

COALESCE：NULL 替代值

-- 如果 nickname 是 NULL，使用 name
SELECT COALESCE(nickname, name) as display_name FROM users;

-- 多個備選
SELECT COALESCE(nickname, name, email, 'Anonymous') FROM users;

NULLIF：轉換為 NULL

-- 如果兩個值相等，返回 NULL
SELECT NULLIF(discount, 0);  -- 0 轉成 NULL，避免除以零

-- 計算平均折扣（排除 0）
SELECT AVG(NULLIF(discount, 0)) FROM products;

NULL 與聚合函數

-- 大多數聚合函數會忽略 NULL
SELECT AVG(price) FROM products;  -- NULL 值不參與計算

-- COUNT 的特殊行為
SELECT COUNT(*) FROM products;        -- 計算所有列
SELECT COUNT(price) FROM products;    -- 只計算 price 非 NULL 的列
SELECT COUNT(DISTINCT price) FROM products;  -- 去重後計算

---

九、 CASE 條件表達式

-- 簡單 CASE
SELECT
    name,
    CASE category
        WHEN 'book' THEN '書籍'
        WHEN 'electronics' THEN '電子產品'
        ELSE '其他'
    END as category_zh
FROM products;

-- 搜尋 CASE（更靈活）
SELECT
    name,
    price,
    CASE
        WHEN price < 100 THEN '便宜'
        WHEN price < 500 THEN '中等'
        ELSE '昂貴'
    END as price_level
FROM products;

-- 在聚合中使用
SELECT
    COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) as inactive_count
FROM users;

---

十、 實用技巧

欄位別名

-- AS 可省略
SELECT name AS product_name FROM products;
SELECT name product_name FROM products;

-- 表別名（簡化長表名）
SELECT p.name, c.name
FROM products p
JOIN categories c ON p.category_id = c.id;

表達式與函數

-- 計算欄位
SELECT name, price, quantity, price * quantity AS total FROM order_items;

-- 字串處理
SELECT UPPER(name), LOWER(email), LENGTH(description) FROM users;

-- 日期處理
SELECT
    name,
    created_at,
    DATE_PART('year', created_at) AS year,
    created_at::DATE AS date_only
FROM users;

---

總結

子句	用途	執行順序
FROM	指定資料來源	1
WHERE	過濾列	2
GROUP BY	分組	3
HAVING	過濾分組	4
SELECT	選擇欄位	5
DISTINCT	去重	6
ORDER BY	排序	7
LIMIT/OFFSET	限制數量	8

---

進階挑戰

1. 寫一個查詢：找出每個類別中價格最高的前 3 個產品（使用 DISTINCT ON 或子查詢）
2. 使用 CASE 表達式，將訂單金額分為「小額」「中額」「大額」三級，並統計各級的數量
3. 比較 OFFSET 和 Keyset Pagination 在 100 萬筆資料時的效能差異

---

延伸閱讀與資源

• PostgreSQL SELECT 官方文件
• SQL 執行順序詳解
• DISTINCT ON 用法

---

← 上一章：表設計與約束 | 返回專題首頁 | 下一章：JOIN 完全攻略 →