三大查詢模式對比

模式	關鍵字	適用場景	結果集
子查詢	IN, EXISTS, =, >/<	單值/集合比較、關聯過濾	單欄或標量
多表聯查	JOIN (INNER/LEFT/RIGHT)	多表關聯、欄位組合	寬表 (多欄位)
聯合查詢	UNION / UNION ALL	同結構結果合併、異構表合併	縱向合併 (同欄數)

1. 子查詢

標量子查詢 (返回單值)

SELECT * FROM orders 
WHERE user_id = (SELECT id FROM users WHERE email = 'a@b.com');

列子查詢 (返回單列多行)

SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 0);

行子查詢 (返回單行多列)

SELECT * FROM t1 
WHERE (col1, col2) = (SELECT col1, col2 FROM t2 WHERE id = 1);

表子查詢 (衍生表)

SELECT d.dept_name, e.emp_count
FROM departments d
JOIN (SELECT dept_id, COUNT(*) AS emp_count FROM employees GROUP BY dept_id) e
  ON d.id = e.dept_id;

相關子查詢 (逐行執行，慎用)

SELECT * FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE user_id = o.user_id);

優化：改用 JOIN + 聚合或視圖。

2. 多表聯查 (JOIN)

語法結構

SELECT select_list
FROM table_a a
[INNER] JOIN table_b b ON a.join_col = b.join_col
LEFT JOIN table_c c ON a.col = c.col
RIGHT JOIN table_d d ON b.col = d.col
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

JOIN 類型圖解

• INNER JOIN：雙方都有匹配行
• LEFT JOIN：保留左表所有行，右表無匹配則 NULL
• RIGHT JOIN：保留右表所有行 (可改寫為 LEFT JOIN)
• CROSS JOIN：笛卡兒積 (無 ON 條件)

實戰範例：訂單-用戶-商品三表聯查

SELECT o.id AS order_id, u.name AS buyer, p.title AS product, o.amount, o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 'paid'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

3. 聯合查詢 (UNION)

-- UNION：去重合併 (較慢)
SELECT id, name, 'user' AS type FROM users WHERE status = 1
UNION
SELECT id, name, 'admin' AS type FROM admins WHERE status = 1;

-- UNION ALL：直接合併，不去重 (高效能)
SELECT id, title, 'article' AS type FROM articles WHERE pub_date >= '2024-01-01'
UNION ALL
SELECT id, title, 'video' AS type FROM videos WHERE pub_date >= '2024-01-01'
ORDER BY pub_date DESC;

規則

• 所有 SELECT 欄位數、順序、型別必須相容
• 欄位名以第一個 SELECT 為準，建議用別名對齊
• ORDER BY/LIMIT 放在最後，作用於合併結果
• 括號可控制優先順序：(SELECT ... UNION SELECT ...) ORDER BY ...

效能優化建議

1. 子查詢轉 JOIN：MySQL 5.6+ 對 IN/EXISTS 有優化，但複雜相關子查詢仍建議改寫
2. 小表驅動大表：LEFT JOIN 以小表為驅動表 (左表)
3. 索引覆蓋：ON/WHERE/GROUP BY/ORDER BY 列建立複合索引
4. 避免 SELECT *：只取需要欄位，減少網路與記憶體
5. UNION ALL 優於 UNION：除非必須去重
6. EXPLAIN 分析：關注 type (ref/range/index)、rows、Extra (Using index/Using where/Using temporary)