SQL for QA — 從 SELECT 到 JOIN、抓 bug 必備 30 個 query 範本

「我看不到 DB 就不能驗 bug」是 QA 的痛。會 SQL 的 QA 不用等 dev 查資料、自己 30 秒拿到答案。這篇給你 QA 角度的 SQL 入門到實戰。

不會 SQL = 每件事都要 Slack 一個 dev。

1. SELECT 基本

-- 列所有欄位
SELECT * FROM users;

-- 列指定欄位
SELECT id, email, created_at FROM users;

-- 限制筆數（一定要加！）
SELECT * FROM users LIMIT 10;

-- 排序
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

⚠️ 看大 table 沒加 LIMIT 會炸 production。

2. WHERE — 過濾

-- 等於
SELECT * FROM users WHERE email = '[email protected]';

-- 不等於
SELECT * FROM users WHERE status != 'active';

-- 比較
SELECT * FROM orders WHERE total > 1000;

-- 範圍
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

-- IN（多選）
SELECT * FROM users WHERE country IN ('TW', 'JP', 'US');

-- LIKE（模糊）
SELECT * FROM users WHERE email LIKE '%@example.com';

-- NULL
SELECT * FROM users WHERE deleted_at IS NULL;

-- 時間
SELECT * FROM orders WHERE created_at >= '2026-06-01';

-- 組合
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '24 hours'
  AND total > 0;

3. COUNT / SUM / AVG

-- 多少 user
SELECT COUNT(*) FROM users;

-- 多少 active user
SELECT COUNT(*) FROM users WHERE status = 'active';

-- 訂單總額
SELECT SUM(total) FROM orders WHERE status = 'paid';

-- 平均訂單
SELECT AVG(total) FROM orders;

-- 最大 / 最小
SELECT MAX(created_at), MIN(created_at) FROM orders;

4. GROUP BY — 分組

-- 每個 status 多少 user
SELECT status, COUNT(*) FROM users GROUP BY status;

-- 每個 country 訂單數 + 總額
SELECT country, COUNT(*), SUM(total)
FROM orders
GROUP BY country
ORDER BY SUM(total) DESC;

-- 每天訂單數
SELECT DATE(created_at), COUNT(*)
FROM orders
WHERE created_at > '2026-06-01'
GROUP BY DATE(created_at)
ORDER BY DATE(created_at);

5. JOIN — 連表（QA 最常卡這裡）

-- INNER JOIN：兩邊都有
SELECT u.email, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-06-01';

-- LEFT JOIN：所有 user、有沒有訂單都顯示
SELECT u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.email
ORDER BY order_count DESC;

90% 的 bug 驗證會用 LEFT JOIN：「這個 user 有沒有相關記錄」。

A. 驗 bug 用（最常用）

-- 1. 查特定 user 的訂單
SELECT * FROM orders WHERE user_id = 1042 ORDER BY created_at DESC LIMIT 20;

-- 2. 查最近 1 小時的失敗訂單
SELECT * FROM orders
WHERE status = 'failed'
  AND created_at > NOW() - INTERVAL '1 hour';

-- 3. 重複訂單（防止重複扣款 bug）
SELECT user_id, amount, COUNT(*)
FROM orders
WHERE created_at > NOW() - INTERVAL '5 minutes'
GROUP BY user_id, amount
HAVING COUNT(*) > 1;

-- 4. 該 user 的 session 歷史
SELECT * FROM sessions
WHERE user_id = 1042
ORDER BY created_at DESC LIMIT 10;

-- 5. 查 audit log
SELECT * FROM audit_logs
WHERE user_id = 1042
  AND action = 'password_change'
ORDER BY created_at DESC;

B. 驗 data integrity

-- 6. 找孤兒記錄（user_id 指向不存在的 user）
SELECT o.* FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

-- 7. 找重複 email
SELECT email, COUNT(*) FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 8. 找 NULL 在不該 NULL 的地方
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- 9. 找負值
SELECT * FROM orders WHERE total < 0 LIMIT 10;

-- 10. 找未來時間（資料污染）
SELECT * FROM orders WHERE created_at > NOW();

C. 驗 migration

-- 11. 新欄位 backfill 進度
SELECT
  COUNT(*) AS total,
  COUNT(new_column) AS filled,
  COUNT(*) - COUNT(new_column) AS pending
FROM users;

-- 12. 欄位最小 / 最大 / NULL 比例
SELECT
  MIN(age), MAX(age), AVG(age),
  COUNT(*) - COUNT(age) AS null_count
FROM users;

-- 13. Enum 值分佈
SELECT status, COUNT(*) FROM orders GROUP BY status;

D. 找 edge case

-- 14. 找 trial 過期但還 active 的 user
SELECT * FROM users
WHERE trial_ends_at < NOW()
  AND status = 'trial_active';

-- 15. 找買了 5+ 訂單的 VIP
SELECT u.email, COUNT(o.id) as cnt
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.email
HAVING COUNT(o.id) >= 5;

-- 16. 找從沒登入過的 user
SELECT * FROM users WHERE last_login_at IS NULL;

-- 17. 邊界值搜尋
SELECT * FROM orders WHERE total IN (0, 0.01, 99999.99);

E. 效能 / 統計

-- 18. 每日新註冊
SELECT DATE(created_at), COUNT(*)
FROM users
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at);

-- 19. Top 10 客戶
SELECT u.email, SUM(o.total) as revenue
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.email
ORDER BY revenue DESC LIMIT 10;

-- 20. P95 訂單金額（PostgreSQL）
SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total)
FROM orders;

F. JSON 欄位查詢（PostgreSQL）

-- 21. JSON 內某 key
SELECT * FROM users WHERE settings->>'theme' = 'dark';

-- 22. JSON nested
SELECT * FROM users WHERE settings->'notifications'->>'email' = 'true';

-- 23. JSON 含某 array item
SELECT * FROM users WHERE settings->'tags' ? 'qa';

G. 進階：CTE + Window

-- 24. CTE — 多步驟查詢
WITH recent_orders AS (
  SELECT user_id, total FROM orders
  WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT u.email, SUM(r.total) as week_total
FROM users u
JOIN recent_orders r ON u.id = r.user_id
GROUP BY u.email;

-- 25. Window — 每個 user 的訂單排序
SELECT
  user_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) as rank
FROM orders;

-- 26. 找每 user 最近一筆訂單
SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

H. 時間操作

-- 27. 時區轉換
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Taipei'
FROM orders LIMIT 10;

-- 28. 今天 / 昨天 / 本週
SELECT * FROM orders WHERE created_at >= CURRENT_DATE;
SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '1 day';
SELECT * FROM orders WHERE created_at >= DATE_TRUNC('week', NOW());

-- 29. 時間差
SELECT
  AVG(EXTRACT(EPOCH FROM (paid_at - created_at)))
FROM orders
WHERE status = 'paid';

I. EXPLAIN — 看為什麼慢

-- 30. 分析 query plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1042;

如果看到 Seq Scan（全表掃描）→ 該欄位該加 index。

永遠不要在 prod 做這些

1. ❌ DELETE FROM users WHERE ...
2. ❌ UPDATE users SET ...
3. ❌ TRUNCATE
4. ❌ DROP TABLE
5. ❌ ALTER TABLE

改資料只能透過 migration script + code review + 跑過 staging。

Read-only 也要小心

-- ❌ 大 table 沒 LIMIT
SELECT * FROM orders;   -- 1000 萬筆 → 連線 timeout

-- ✅ 永遠加 LIMIT
SELECT * FROM orders LIMIT 100;

-- ❌ 沒 index 的 LIKE
SELECT * FROM orders WHERE notes LIKE '%abc%';  -- 全表掃描

-- ✅ 看 plan 再跑
EXPLAIN SELECT * FROM orders WHERE notes LIKE '%abc%';

用 read replica 不要打 master

如果公司有 read replica（slave），用 replica connection。

強烈建議 QA 申請 read-only DB account + Metabase / Redash。

-- PostgreSQL：列所有 table
\dt

-- 看某 table 結構
\d orders

-- MySQL
SHOW TABLES;
DESCRIBE orders;

-- 看欄位資訊
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders';

-- 看 index
SELECT * FROM pg_indexes WHERE tablename = 'orders';

不熟 schema 寫不出對的 query。第一週進公司花 1 天讀 schema 是值得的。

該找 dev 的時機

1. UPDATE / DELETE / migration
2. 你的 query 跑了 30 秒還沒回
3. 大量 lock 風險（thousand rows）
4. 不確定 schema 語意

• SQL Bolt — 互動式教學
• HackerRank SQL — 題目
• LeetCode Database — 進階
• PostgreSQL Tutorial — 完整 ref
• DB Fiddle — 在線跑 SQL

1. 會 SQL 的 QA = 自己解決 80% 資料問題
2. 永遠 read-only、永遠 LIMIT、永遠看 EXPLAIN
3. JOIN 是分水嶺、學會就上一個 level
4. 第一週進公司讀 schema、後面省一年
5. prod 寫操作一律走 migration、不走 query

SQL 是 QA 最高 ROI 的硬技能 — 一週能上手、十年用得到、薪資 +20%。從每天看一張 schema、每週寫 5 個 query 開始，三個月後你會發現自己變「會解資料問題的 QA」、不是「只會點 button 的 QA」。