学ぶ / チートシート
テスターのための SQL チートシート
UI や API の裏側にあるデータを検証するためにテスターが使う SQL です。レコードを見つけ、変更されたことを確認し、あってはならない重複、孤立レコード、NULL を探します。構文は PostgreSQL、MySQL 8、SQLite で動く標準 SQL です。データベースによって異なる点は注記に記載しています。
標準 SQL。PostgreSQL 16、MySQL 8、SQLite 3 で確認済み で確認済み
行を検索する
| やりたいこと | コード |
|---|---|
| テーブルから列を取得 | SELECT id, email FROM users; |
| 絞り込み | SELECT * FROM orders WHERE status = 'paid' AND total > 100; |
| 複数の値のいずれか | WHERE status IN ('paid', 'shipped') |
| 範囲（両端を含む） | WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' |
| パターン | WHERE email LIKE '%@example.com' |
| 値がない | WHERE phone IS NULL |
| 新しい順に並べる | ORDER BY created_at DESC |
| 先頭 10 行 | LIMIT 10 |
| 重複のない値 | SELECT DISTINCT status FROM orders; |
SQL Server では LIMIT の代わりに SELECT TOP 10 を使います。タイムスタンプに対する BETWEEN は終了日の 0 時ちょうどまでしか含みません。日単位で丸ごと含めたいなら「< 翌日」を使います。
集計
| やりたいこと | コード |
|---|---|
| 行数を数える | SELECT COUNT(*) FROM orders; |
| NULL 以外の値を数える | SELECT COUNT(shipped_at) FROM orders; |
| 重複を除いて数える | SELECT COUNT(DISTINCT user_id) FROM orders; |
| グループごとの合計 | SELECT status, COUNT(*), SUM(total) FROM orders GROUP BY status; |
| グループを絞り込む | SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5; |
| 最小・最大・平均 | SELECT MIN(total), MAX(total), AVG(total) FROM orders; |
WHERE はグループ化の前に行を絞り込み、HAVING はグループ化の後にグループを絞り込みます。
結合
| やりたいこと | コード |
|---|---|
| 一致する行のみ | SELECT o.id, u.email FROM orders o JOIN users u ON u.id = o.user_id; |
| 左側の行をすべて残す | SELECT u.email, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id; |
| 一致しない行（アンチ結合） | SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL; |
| 同じことを NOT EXISTS で | SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); |
1 対多のキーで結合すると「1」側の行が繰り返されます。結合後に SUM() が大きすぎるように見えたら、結合で行が増殖（ファンアウト）しています。
テスターがよく実行するデータチェック
| やりたいこと | コード |
|---|---|
| 重複 | SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; |
| 孤立レコード（親のない子） | SELECT o.* FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL; |
| 必須項目が空 | SELECT * FROM users WHERE email IS NULL OR TRIM(email) = ''; |
| 範囲外の値 | SELECT * FROM order_items WHERE quantity <= 0 OR unit_price < 0; |
| ヘッダーの合計 ≠ 明細行の合計 | SELECT o.id FROM orders o JOIN order_items i ON i.order_id = o.id GROUP BY o.id, o.total HAVING o.total <> SUM(i.quantity * i.unit_price); |
| UI 操作後に行が存在するか | SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 1; |
NULL の落とし穴
| 落とし穴 | 何が起きるか |
|---|---|
| WHERE col = NULL | 決して真になりません。IS NULL / IS NOT NULL を使います。 |
| WHERE status <> 'paid' | status が NULL の行は除外されます。含めたい場合は OR status IS NULL を追加します。 |
| NOT IN（NULL を返すサブクエリ） | 1 行も返りません。代わりに NOT EXISTS を使います。 |
| COUNT(col) と COUNT(*) | COUNT(col) は NULL を数えず、COUNT(*) はすべての行を数えます。 |
| NULL を含む算術演算 | price + NULL は NULL になります。0 が妥当なデフォルトなら COALESCE(col, 0) で囲みます。 |
ウィンドウ関数
| やりたいこと | コード |
|---|---|
| グループごとに行番号を振る | ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) |
| グループごとの最新行 | SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders o) t WHERE rn = 1; |
| 同順位ありの順位付け | RANK() OVER (ORDER BY total DESC) / DENSE_RANK() … |
| 前の行の値 | LAG(status) OVER (PARTITION BY test_name ORDER BY run_at) |
| 累計 | SUM(total) OVER (ORDER BY created_at) |
PostgreSQL、MySQL 8 以降、SQLite 3.25 以降、SQL Server でサポートされています。ウィンドウ関数は WHERE では使えないので、外側のクエリで絞り込みます。
データを安全に変更する
| やりたいこと | コード |
|---|---|
| 挿入 | INSERT INTO users (email, name) VALUES ('qa+1@example.com', 'QA One'); |
| 更新（必ず WHERE を付ける） | UPDATE orders SET status = 'cancelled' WHERE id = 1043; |
| 削除（必ず WHERE を付ける） | DELETE FROM sessions WHERE user_id = 42; |
| 試してから元に戻す | BEGIN; UPDATE orders SET status = 'cancelled' WHERE id = 1043; SELECT status FROM orders WHERE id = 1043; ROLLBACK; |
UPDATE や DELETE の前に、同じ WHERE で SELECT を実行し、行数を確認しましょう。