SELECT 是 SQL 核心，本文從單表查詢進階至多表 JOIN、彙總分群、分頁、子查詢、CTE。

基本查詢

SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;

多表 JOIN

-- 內連接：僅符合關聯的列
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- 左連接：保留左表所有列
SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

彙總與分群

SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 5
ORDER BY post_count DESC;

子查詢與 CTE (MySQL 8.0+ / PostgreSQL / SQL Server)

-- CTE 簡化複雜查詢
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT au.name, COUNT(p.id) AS cnt
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.id, au.name;

分頁查詢

-- MySQL / PostgreSQL
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;

-- SQL Server / Oracle 12c+ / PostgreSQL
SELECT * FROM posts ORDER BY id DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

實務效能建議

1. 避免 SELECT *，明確列出需要欄位。
2. WHERE/JOIN/ORDER BY 欄位加索引。
3. 大偏移分頁改用游標分頁：WHERE id > last_id ORDER BY id LIMIT 20。
4. 適度使用覆蓋索引 (Covering Index) 避免回表。
5. EXPLAIN 分析執行計畫，關注 type、rows、Extra。