2026年9月 改訂にあたって
2019年に投稿した記事を書き直しました。
当時は「句ごとにテストケースを作る」という書き方をしていましたが、実際の現場でそこまでやることはまずありません。そこで、この記事は 「全部やるテストケース集」ではなく「SELECT文を読むときに疑う観点の一覧」 として整理し直しました。あわせて、テストしやすいSELECT文の書き方 も追加しています。変更点は末尾の「改訂履歴」にまとめています。
きっかけ
あるとき、○○ちゃんに、「SQLの単体テストケースってどうやって考えればよいの？」と問われ、「うっ」となりました。
「網羅的なテストぉー！」だとか、「カバレッジはどのくらいを狙っているの？」などと偉そうに言ってきたが、なんとなくごまかしていたことを整理しています。
テスト対象はDDLなのか、DMLなのか。日常よく使うSELECT文を単体テストケースの考え方の対象とします。
考えてみると、 SELECT文の挙動を言語化できていなかったから です。挙動を言葉で説明できなければ、SELECT文のどのあたりで、どんなバグが起きるかも想像できず、テスト観点も出せないなぁと。
この記事の使い方
- SELECT文で起きるバグの多くは、書き間違いより 挙動の思い込み から生まれると考えています。「LEFT JOINだから左の行は全部残るはず」「NOT INで除外できるはず」など。
- なので、SELECT文の句ごとに 挙動 → 思い込みやすい落とし穴 → テスト観点 の順で整理します。
- 落とし穴のSQLは、やりがちな書き方(NG)と、直した書き方(OK)を対で 示します。どちらが正しいかについては、実際は仕様次第なですが、「よくありそうな意図で見て」NG/OKの判断をしています。
- この観点は、句ごとに分解して考えていますが、全ての句をこの観点でテストするためのものではありません。 全部やってたら時間がいくらあっても足りないですし、メンドクサイです。対象のSQLを読んで、当てはまる観点だけ拾ってください。
- 使いどころは次の3つです。
- テストデータ設計：意地悪なデータを混ぜるためのチェックリスト
- コードレビュー：SQLを読むときの着眼点
- 設計：制約や仕様を決めて、観点そのものを消す
- 対象は SELECT文が返す結果の正しさ です。性能(実行計画・インデックス)、ロック(FOR UPDATE)、同時実行、権限は扱いません。
- SQLの実行結果は、特に断りのない限り PostgreSQL 16 で確認しています。NULLをORDER BYしたときの並び順などはRDBMSによって違うので、お使いの製品で確かめてください。
- この記事のSQLは筆者の命名規約で書いています。列名は英大文字で、末尾にドメインを表す接尾辞を付けます(
_ID：ID、_CD：コード、_NM：名称、_AMT：金額、_CNT：件数、_NO：番号、_TS：日時、_DT：日付、_FLG：フラグ)。テーブルはT_、ビューはV_で始めます。 - PostgreSQLは引用符なしの識別子を小文字に変換するので、実際の結果の列名は小文字で表示されます。記事中の結果例は、規約に合わせて大文字で書いています。
前提1：SELECT文の論理的な評価順序
SELECT文は、書いた順ではなく次の順で 論理的に 評価されます1。
| 順序 | 句 | 何をするか |
|---|---|---|
| 1 | FROM / JOIN / ON | 元になるワークテーブルを作る |
| 2 | WHERE | 行を絞る |
| 3 | GROUP BY | 行をグループに集約する |
| 4 | HAVING | グループを絞る |
| 5 | SELECT | 列・式を計算する(ウィンドウ関数もここ) |
| 6 | DISTINCT | 重複行を除く |
| 7 | UNION / INTERSECT / EXCEPT | 集合演算 |
| 8 | ORDER BY | 並べ替える |
| 9 | LIMIT / OFFSET / FETCH | 件数を切る |
実際のRDBMSは実行計画に沿って効率よく処理するので、内部の処理順は違います。ただ、結果はこの順で処理したのと同じになる ことが保証されているので、挙動を考えるときはこの順序で考えればOKです。
この順序から、たとえば次のことがわかります。
- SELECT句で付けた別名は、WHERE句では使えない(まだ存在しないから)2
- WHEREで絞った後にGROUP BYするので、集約前に絞るならWHERE、集約結果で絞るならHAVING
- LIMITはORDER BYの後なので、ORDER BYが不十分だと「どの行が切られるか」が不定になる
そしてもう1つ、テストにとって大事なことがあります。各句は「前の句が作ったワークテーブル」だけを入力にしています。 つまり、前の句の結果をイメージしたテーブルを用意すれば、その句だけを取り出してテストできます。これは後の「テストしやすいSELECT文の書き方」で活用します。
前提2：SQLは3値論理
SQLの比較演算の結果は、TRUE / FALSE / UNKNOWN の3つです(3値論理は『プログラマのためのSQL 第4版』で詳しく解説されています)。NULLとの比較は、= でも <> でも UNKNOWN になります。
そして、WHERE・ON・HAVINGは、TRUEの行だけを残します。 FALSEとUNKNOWNは同じように捨てられます。SELECT文の落とし穴の多くは、この「UNKNOWNも捨てられる」から生まれます。
3値論理の真理値表
NOT
| X | NOT X |
|---|---|
| TRUE | FALSE |
| UNKNOWN | UNKNOWN |
| FALSE | TRUE |
AND
| TRUE | UNKNOWN | FALSE | |
|---|---|---|---|
| TRUE | TRUE | UNKNOWN | FALSE |
| UNKNOWN | UNKNOWN | UNKNOWN | FALSE |
| FALSE | FALSE | FALSE | FALSE |
OR
| TRUE | UNKNOWN | FALSE | |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
| FALSE | TRUE | UNKNOWN | FALSE |
句ごとのテスト観点
FROM / JOIN
挙動
概念的には、FROM句のテーブルをクロス結合したワークテーブルから、ON句の条件がTRUEになる行だけを残します3。LEFT OUTER JOINの場合は、それに加えて「右側に相手が見つからなかった左側の行」を、右側の列をNULLにして残します。
落とし穴1：結合で行が増える
1対多で結合すると、左側の1行が右側の件数分に増えます。これに気づかず集約すると、金額が何倍にもなります。
-- NG：結合してから集約する。顧客1の予算は1000なのに、注文が3件あるので3000になる
WITH T_CUSTOMER(CUSTOMER_ID, BUDGET_AMT) AS (VALUES (1, 1000))
, T_ORDER(CUSTOMER_ID, ORDER_AMT) AS (VALUES (1, 100), (1, 200), (1, 300))
SELECT
C.CUSTOMER_ID
, SUM(C.BUDGET_AMT) AS BUDGET_AMT
FROM
T_CUSTOMER C
JOIN T_ORDER O
ON O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY
C.CUSTOMER_ID;
-- CUSTOMER_ID | BUDGET_AMT
-- -------------+------------
-- 1 | 3000
-- OK：多側を先に集約して1対1にしてから結合する
WITH T_CUSTOMER(CUSTOMER_ID, BUDGET_AMT) AS (VALUES (1, 1000))
, T_ORDER(CUSTOMER_ID, ORDER_AMT) AS (VALUES (1, 100), (1, 200), (1, 300))
, W_ORDER_SUM AS (SELECT CUSTOMER_ID, SUM(ORDER_AMT) AS ORDER_AMT FROM T_ORDER GROUP BY CUSTOMER_ID)
SELECT
C.CUSTOMER_ID
, C.BUDGET_AMT
, COALESCE(S.ORDER_AMT, 0) AS ORDER_AMT
FROM
T_CUSTOMER C
LEFT JOIN W_ORDER_SUM S
ON S.CUSTOMER_ID = C.CUSTOMER_ID;
-- CUSTOMER_ID | BUDGET_AMT | ORDER_AMT
-- -------------+------------+-----------
-- 1 | 1000 | 600
落とし穴2：外部結合がWHEREで内部結合に化ける
LEFT JOINの右側テーブルの列をWHEREで絞ると、右側がNULLの行(=相手がいなかった左側の行)がUNKNOWNで捨てられ、結果的にINNER JOINと同じになります。
-- NG：WHEREに書くと、顧客1しか残らない(実質INNER JOIN)
WITH T_CUSTOMER(CUSTOMER_ID) AS (VALUES (1), (2), (3))
, T_ORDER(ORDER_ID, CUSTOMER_ID, STATUS_CD) AS (VALUES (10, 1, '01'), (11, 1, '02'), (12, 2, '02'))
SELECT
C.CUSTOMER_ID
, O.ORDER_ID
FROM
T_CUSTOMER C
LEFT JOIN T_ORDER O
ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE
O.STATUS_CD = '01';
-- CUSTOMER_ID | ORDER_ID
-- -------------+----------
-- 1 | 10
-- OK：ONに書くと、顧客1,2,3が全部残る(2,3のORDER_IDはNULL)
WITH T_CUSTOMER(CUSTOMER_ID) AS (VALUES (1), (2), (3))
, T_ORDER(ORDER_ID, CUSTOMER_ID, STATUS_CD) AS (VALUES (10, 1, '01'), (11, 1, '02'), (12, 2, '02'))
SELECT
C.CUSTOMER_ID
, O.ORDER_ID
FROM
T_CUSTOMER C
LEFT JOIN T_ORDER O
ON O.CUSTOMER_ID = C.CUSTOMER_ID
AND O.STATUS_CD = '01';
-- CUSTOMER_ID | ORDER_ID
-- -------------+----------
-- 1 | 10
-- 2 | NULL
-- 3 | NULL
ここは仕様次第です。どちらも正解になりえます。「注文のない顧客も一覧に出したい」ならON、「ステータス01の注文がある顧客だけ見たい」ならWHEREです。後者なら、最初からINNER JOINと書くほうが意図が伝わります。LEFT JOINとWHEREが同居していたら、書いた人の意図を確認する のがレビューの勘所と思います。
落とし穴3：IN を JOIN に書き換えると行が増える
IN や EXISTS は「相手がいるかどうか」を見るだけなので、相手が何件あっても左側の行は1行のままです。性能改善などで JOIN に書き換えると、相手の件数分だけ行が増えます。
-- NG：品目001は注文が2件あるので、JOINに書き換えると2行になる
WITH T_ITEM(ITEM_CD) AS (VALUES ('001'), ('002'))
, T_ORDER(ITEM_CD) AS (VALUES ('001'), ('001'))
SELECT
(SELECT COUNT(*) FROM T_ITEM WHERE ITEM_CD IN (SELECT ITEM_CD FROM T_ORDER)) AS IN_CNT
, (SELECT COUNT(*) FROM T_ITEM I JOIN T_ORDER O ON O.ITEM_CD = I.ITEM_CD) AS JOIN_CNT;
-- IN_CNT | JOIN_CNT
-- --------+----------
-- 1 | 2
-- OK：「あるかどうか」だけを見たいならEXISTSにする(相手が何件でも1行のまま)
WITH T_ITEM(ITEM_CD) AS (VALUES ('001'), ('002'))
, T_ORDER(ITEM_CD) AS (VALUES ('001'), ('001'))
SELECT
COUNT(*)
FROM
T_ITEM I
WHERE
EXISTS (SELECT 1 FROM T_ORDER O WHERE O.ITEM_CD = I.ITEM_CD);
-- COUNT
-- --------
-- 1
テスト観点
| 観点 | 用意するデータ | 確認すること |
|---|---|---|
| 結合条件の一致・不一致 | 結合キーが一致する行、一致しない行 | 一致する行だけが結合される |
| 片側だけにある行 | 左だけにある行、右だけにある行 | INNERなら消える、LEFTなら左だけ残り右の列はNULL |
| 結合キーのNULL | 結合キーがNULLの行 | NULL同士は結合されない(UNKNOWN) |
| 行の増殖 | 右側に同じキーが複数ある行 | 件数が意図どおりか、集約値が膨らんでいないか |
| IN・EXISTS との違い | 相手が複数件ある行 | JOINに書き換えた場合に行が増えていないか |
| 外部結合の絞り込み | 右側の条件に合わない行 | 絞り込みをONとWHEREのどちらに書くべきか |
| 式を使った結合条件 | 関数・型変換・範囲条件で結合する行 | 境界・NULL・型変換の結果が意図どおりか |
テストのしやすさ：結合が複雑なら、SELECT句を主キーだけにし、WHERE以降を外したSQLで、結合結果の行だけを確認できます。結合の確認と、その後の絞り込み・集約の確認を分けられます。
WHERE
挙動
FROM句で作ったワークテーブルの各行に検索条件を適用し、TRUEの行だけ を残します。FALSEとUNKNOWNの行は捨てられます。
落とし穴1：<> でNULLの行が消える
-- NG：「ステータス01以外」のつもりが、ステータスがNULLの行も消える
WITH T_ORDER(STATUS_CD) AS (VALUES ('01'), ('02'), (NULL))
SELECT
COUNT(*)
FROM
T_ORDER
WHERE
STATUS_CD <> '01';
-- ('02'だけ。NULLの行は UNKNOWN で捨てられる)
-- COUNT
-- --------
-- 1
-- OK：NULLの行も「01以外」として残す
WITH T_ORDER(STATUS_CD) AS (VALUES ('01'), ('02'), (NULL))
SELECT
COUNT(*)
FROM
T_ORDER
WHERE
STATUS_CD <> '01'
OR STATUS_CD IS NULL;
-- COUNT
-- --------
-- 2
-- OK：PostgreSQLなら IS DISTINCT FROM でも書ける(Oracleでは使えません)
WITH T_ORDER(STATUS_CD) AS (VALUES ('01'), ('02'), (NULL))
SELECT
COUNT(*)
FROM
T_ORDER
WHERE
STATUS_CD IS DISTINCT
FROM
'01';
-- COUNT
-- --------
-- 2
もちろん「NULLは対象外」が仕様ならNG側が正解です。大事なのは、NULLの行をどちらに含めるかを意識して書いているか です。
落とし穴2：NOT IN のサブクエリにNULLが混じると0件になる
これは超定番です。X NOT IN ('001', NULL) は X <> '001' AND X <> NULL と同じで、後半が必ずUNKNOWNになるので、全体がTRUEになりません。
-- NG：販売停止品目以外の品目を数えたいのに、1件も返らない
WITH T_ITEM(ITEM_CD) AS (VALUES ('001'), ('002'), ('003'))
, T_STOP_ITEM(ITEM_CD) AS (VALUES ('001'), (NULL))
SELECT
COUNT(*)
FROM
T_ITEM
WHERE
ITEM_CD NOT IN (SELECT ITEM_CD FROM T_STOP_ITEM);
-- ('002'と'003'が数えられてほしい)
-- COUNT
-- --------
-- 0
-- OK：NOT EXISTS にする(サブクエリにNULLが混じっても影響を受けない)
WITH T_ITEM(ITEM_CD) AS (VALUES ('001'), ('002'), ('003'))
, T_STOP_ITEM(ITEM_CD) AS (VALUES ('001'), (NULL))
SELECT
COUNT(*)
FROM
T_ITEM I
WHERE
NOT EXISTS (SELECT 1 FROM T_STOP_ITEM S WHERE S.ITEM_CD = I.ITEM_CD);
-- COUNT
-- --------
-- 2
NOT IN のまま使うなら、サブクエリ側に WHERE ITEM_CD IS NOT NULL を足すか、そもそもその列を NOT NULL にします。ただ、条件を足し忘れても静かに0件になるだけなので、最初から NOT EXISTS で書く ほうが安全です。
落とし穴3：日時の範囲の終端
timestamp型の列を日付だけで BETWEEN すると、最終日の0時0分しか含まれません。
-- NG：9月のデータを取るつもりが、30日の0時より後が漏れる
WITH T_ORDER(ORDER_TS) AS (VALUES (TIMESTAMP '2026-09-30 00:00'), (TIMESTAMP '2026-09-30 15:00'))
SELECT
COUNT(*)
FROM
T_ORDER
WHERE
ORDER_TS BETWEEN '2026-09-01' AND '2026-09-30';
-- COUNT
-- --------
-- 1
-- OK：終端は「翌月の1日より前」と書く
WITH T_ORDER(ORDER_TS) AS (VALUES (TIMESTAMP '2026-09-30 00:00'), (TIMESTAMP '2026-09-30 15:00'))
SELECT
COUNT(*)
FROM
T_ORDER
WHERE
ORDER_TS >= '2026-09-01'
AND ORDER_TS < '2026-10-01';
-- COUNT
-- --------
-- 2
BETWEEN は両端を含むので、日付だけを扱う DATE 型の列には向いていますが、時刻を持つ列には使いにくいです。時刻を持つ列は「以上・未満」(半開区間)で書くと、月末が何日でも、うるう年でも同じ書き方で済みます。
落とし穴4：AND と OR の優先順位
ANDはORより先に評価されます。
-- NG：「01か02で、かつキャンセルされていない」のつもり
-- 実際は「01」または「02かつキャンセルなし」と解釈され、キャンセル済みの01が残る
WITH T_ORDER(ORDER_ID, STATUS_CD, CANCEL_FLG) AS (VALUES (10, '01', '1'), (11, '02', '0'), (12, '02', '1'))
SELECT
ORDER_ID
FROM
T_ORDER
WHERE
STATUS_CD = '01'
OR STATUS_CD = '02'
AND CANCEL_FLG = '0';
-- ORDER_ID
-- -----------
-- 10
-- 11
-- OK：ORの範囲を括弧で囲む
WITH T_ORDER(ORDER_ID, STATUS_CD, CANCEL_FLG) AS (VALUES (10, '01', '1'), (11, '02', '0'), (12, '02', '1'))
SELECT
ORDER_ID
FROM
T_ORDER
WHERE
(STATUS_CD = '01' OR STATUS_CD = '02')
AND CANCEL_FLG = '0';
-- ORDER_ID
-- -----------
-- 11
意図が正しくても、括弧を付けておくとレビューで読み違えられません。STATUS_CD IN ('01', '02') と書ければ、なお読みやすくなります。
落とし穴5：型の暗黙変換と、固定長文字列の末尾空白
-- NG：CHARとVARCHARで、末尾空白の扱いが違う
SELECT
-- true (CHARは末尾空白を無視)
'AB' ::CHAR (5) = 'AB ' ::CHAR (5) AS CHAR_EQ
,
-- false (VARCHARは区別する)
'AB' ::VARCHAR = 'AB ' ::VARCHAR AS VARCHAR_EQ;
-- CHAR_EQ | VARCHAR_EQ
-- ---------+-------------
-- TRUE | FALSE
-- OK：型を揃えるか、比較前に末尾空白を落とす
SELECT
-- true
RTRIM('AB ' ::VARCHAR) = 'AB' AS OK_EQ;
-- OK_EQ
-- -------
-- TRUE
- 固定長のファイルを取り込んだテーブルや、CHARとVARCHARの列を結合するときに効いてきます。
- 文字列のコードと数値を比べる
ITEM_CD = 1の扱いも、製品によって違います。PostgreSQLはエラーにしますが、Oracleなどは暗黙に数値へ変換するので、'001'も'1'も一致し、数値にできない値が混じると実行時エラーになります。ITEM_CD = '001'のように、比較する両辺の型を揃える のが基本です。
テスト観点
| 観点 | 用意するデータ | 確認すること |
|---|---|---|
| 条件の真偽 | TRUE・FALSE・UNKNOWNになる行 | TRUEの行だけが残る |
| 否定条件とNULL |
<>、NOT IN、NOT LIKE の対象列がNULLの行 |
NULLの行を残したいのか捨てたいのか |
| NOT IN のサブクエリ | サブクエリの結果にNULLが混じるデータ | 0件にならないか(NOT EXISTSにすべきか) |
| 境界値 | 範囲の始端・終端ちょうど、その前後の値 | 以上/より大きい、以下/未満が仕様どおりか |
| 日時の終端 | 最終日の0時以降の時刻を持つ行 | 最終日の行が漏れていないか |
| AND/ORの組み合わせ | 括弧の有無で結果が変わる行 | 意図どおりの優先順位か |
| 型の不一致 | 文字列と数値、日付と文字列を比べる条件 | 暗黙変換で一致しすぎたり、エラーになったりしないか |
| 末尾の空白 | 末尾に空白を含む値、CHARとVARCHARの比較 | 空白の有無で一致・不一致が変わらないか |
テストのしやすさ：WHERE句の入力は「FROM句の結果」だけです。結合を実際に動かさなくても、結合後をイメージした1つのテーブル を用意すれば、検索条件だけをテストできます。WHERE句を狙って単体テストするなら、上記でやってきたみたいにWITH句で単体テストデータを作ってテストすればよいです。
GROUP BY と集約関数
挙動
GROUP BYに指定した列の値が同じ行を、1つの行に集約します。このとき NULLも1つのグループ になります。集約関数は、グループごとに計算されます。
落とし穴1：集約関数はNULLを無視する
COUNT(*) は行数を数えますが、COUNT(列) はNULLでない行だけを数えます。AVG もNULLを除いて平均します。
-- NG：NULLを0と見なして平均したいのに、NULLの行が分母から除かれる
WITH T_ORDER(DEPT_CD, ORDER_AMT) AS (VALUES ('A', 10), ('A', NULL), (NULL, 5), (NULL, 7))
SELECT
DEPT_CD
, COUNT(*) AS ROW_CNT
, COUNT(ORDER_AMT) AS AMT_CNT
, AVG(ORDER_AMT) AS AVG_AMT
FROM
T_ORDER
GROUP BY
DEPT_CD
ORDER BY
DEPT_CD;
-- DEPT_CD | ROW_CNT | AMT_CNT | AVG_AMT
-- ---------+---------+---------+---------
-- A | 2 | 1 | 10 ← 2行あるのに 10÷1
-- NULL | 2 | 2 | 6 ← NULLも1グループになる
-- OK：集約する前にNULLを0にする
WITH T_ORDER(DEPT_CD, ORDER_AMT) AS (VALUES ('A', 10), ('A', NULL), (NULL, 5), (NULL, 7))
SELECT
DEPT_CD
, AVG(COALESCE(ORDER_AMT, 0)) AS AVG_AMT
FROM
T_ORDER
GROUP BY
DEPT_CD
ORDER BY
DEPT_CD;
-- DEPT_CD | AVG_AMT
-- ---------+---------
-- A | 5 ← 10÷2
-- NULL | 6
「金額が未入力の行は平均の対象外」が仕様ならNG側が正解です。NULLを0と見なすのか、対象外にするのかを仕様で決めてから書きます。
落とし穴2：0件のときの集約結果
GROUP BYなしで集約すると、対象が0件でも 1行 返り、COUNT は0、SUM や AVG は NULL になります。一方、GROUP BYありなら0行です。
-- NG：合計0を期待していると、SUMがNULLになり後続の計算まで巻き込まれる
WITH T_ORDER(ORDER_AMT) AS (SELECT 1 WHERE FALSE)
-- 0件のテーブル
SELECT
COUNT(*) AS ORDER_CNT
, SUM(ORDER_AMT) AS TOTAL_AMT
FROM
T_ORDER;
-- ORDER_CNT | TOTAL_AMT
-- -----------+-----------
-- 0 | NULL
-- OK：0件のときは0として扱う
WITH T_ORDER(ORDER_AMT) AS (SELECT 1 WHERE FALSE)
SELECT
COUNT(*) AS ORDER_CNT
, COALESCE(SUM(ORDER_AMT), 0) AS TOTAL_AMT
FROM
T_ORDER;
-- ORDER_CNT | TOTAL_AMT
-- -----------+-----------
-- 0 | 0
なお、GROUP BYを付けると、そもそも0行になります。「1行も返らない」のか「0が1行返る」のかで、呼び出し側の作りが変わります。
WITH T_ORDER(ORDER_AMT) AS (SELECT 1 WHERE FALSE)
SELECT
COUNT(*)
FROM
T_ORDER
GROUP BY
ORDER_AMT;
-- (0 rows)
テスト観点
| 観点 | 用意するデータ | 確認すること |
|---|---|---|
| 集約の単位 | 同じ値の行が複数あるグループと、1行だけのグループ | 同じ値の行が1行に集約される |
| GROUP BY列のNULL | GROUP BY列がNULLの行(複数列なら列ごとに) | NULLのグループを出したいのか |
| 集約対象のNULL | 集約する列がNULLの行 | COUNT・AVGがNULLを除いた結果で良いか |
| 0件 | 条件に合う行が1件もないデータ | 1行返るのか0行か、SUMがNULLで良いか |
| 結合による水増し | 結合で増えた行を集約するデータ | 金額や件数が二重計上されていないか |
HAVING
挙動
GROUP BYで集約した後のグループごとに条件を適用し、TRUEのグループだけを残します。GROUP BYがない場合は、テーブル全体を1つのグループとして扱います4。
落とし穴1：絞る位置がWHEREとHAVINGで入れ替わる
WHEREは集約する前、HAVINGは集約した後に効きます。絞る位置が違うと、集約の対象が変わって結果も変わります。
-- NG：「有効な注文(ステータス01)が2件以上ある顧客」のつもり
-- 集約してから件数を見ているので、キャンセル分(09)まで数に入っている
WITH T_ORDER(ORDER_ID, CUSTOMER_ID, STATUS_CD) AS (VALUES (10, 1, '01'), (11, 1, '09'), (12, 2, '01'), (13, 2, '01'))
SELECT
CUSTOMER_ID
, COUNT(*) AS ORDER_CNT
FROM
T_ORDER
GROUP BY
CUSTOMER_ID
HAVING
COUNT(*) >= 2
ORDER BY
CUSTOMER_ID;
-- CUSTOMER_ID | ORDER_CNT
-- -------------+-----------
-- 1 | 2 ← 有効な注文は1件しかないのに出てしまう
-- 2 | 2
-- OK：WHEREで絞ってから集約する
WITH T_ORDER(ORDER_ID, CUSTOMER_ID, STATUS_CD) AS (VALUES (10, 1, '01'), (11, 1, '09'), (12, 2, '01'), (13, 2, '01'))
SELECT
CUSTOMER_ID
, COUNT(*) AS ORDER_CNT
FROM
T_ORDER
WHERE
STATUS_CD = '01'
GROUP BY
CUSTOMER_ID
HAVING
COUNT(*) >= 2
ORDER BY
CUSTOMER_ID;
-- CUSTOMER_ID | ORDER_CNT
-- -------------+-----------
-- 2 | 2
行を絞るのはWHERE、グループを絞るのはHAVING です。集約前に絞れる条件をHAVINGに書くと、結果が変わるうえ、読みにくく遅くなりがちです。
落とし穴2：集約結果がNULLのグループが消える
集約対象が全部NULLだと SUM はNULLを返し、HAVINGの条件はUNKNOWNになってそのグループが捨てられます。
-- NG：「合計1000円未満の顧客」を抽出したいのに、金額が全部未入力の顧客2が漏れる
WITH T_ORDER(CUSTOMER_ID, ORDER_AMT) AS (VALUES (1, 600), (1, 500), (2, NULL ::INT), (2, NULL))
SELECT
CUSTOMER_ID
, SUM(ORDER_AMT) AS TOTAL_AMT
FROM
T_ORDER
GROUP BY
CUSTOMER_ID
HAVING
SUM(ORDER_AMT) < 1000;
-- (0 rows)
-- OK：NULLを0と見なしてから比べる
WITH T_ORDER(CUSTOMER_ID, ORDER_AMT) AS (VALUES (1, 600), (1, 500), (2, NULL ::INT), (2, NULL))
SELECT
CUSTOMER_ID
, COALESCE(SUM(ORDER_AMT), 0) AS TOTAL_AMT
FROM
T_ORDER
GROUP BY
CUSTOMER_ID
HAVING
COALESCE(SUM(ORDER_AMT), 0) < 1000;
-- CUSTOMER_ID | TOTAL_AMT
-- -------------+-----------
-- 2 | 0
「金額が1件も入っていない顧客は対象外」が仕様ならNG側が正解です。ここも、NULLのグループをどちらに含めるかを決めてから書きます。
テスト観点
| 観点 | 用意するデータ | 確認すること |
|---|---|---|
| 条件の真偽 | 集約結果がTRUE・FALSE・UNKNOWNになるグループ | TRUEのグループだけが残る |
| 境界値 | 集約結果が閾値ちょうど、その前後になるグループ | 以上/より大きいが仕様どおりか |
| 集約結果のNULL | 集約対象が全部NULLのグループ | そのグループを残したいのか |
SELECT
挙動
ここまでで残ったワークテーブルに対して、列・式・関数・スカラサブクエリ・ウィンドウ関数を計算します。SELECT句に現れない列は捨てられます。DISTINCTがあれば、重複行を1行に減らします5。
落とし穴1：NULLは式全体に伝わる
-- NG：どれか1つがNULLだと、式全体がNULLになる
SELECT
NULL + 1 AS PLUS_AMT
, 'a' || NULL AS CAT_NM;
-- PLUS_AMT | CAT_NM
-- ----------+--------
-- NULL | NULL
-- OK：NULLの既定値を決めてから計算する
SELECT
COALESCE(NULL ::INT, 0) + 1 AS PLUS_AMT
, 'a' || COALESCE(NULL, '') AS CAT_NM
-- CONCATはNULLを空文字として扱う
, CONCAT('a', NULL) AS CONCAT_NM;
-- PLUS_AMT | CAT_NM | CONCAT_NM
-- ----------+--------+-----------
-- 1 | a | a
なお、Oracleは空文字 '' をNULLとして扱い、'a' || NULL は 'a' になります。連結のNULLの扱いは製品差が大きいので、移行のときは特に注意が必要です。
落とし穴2：整数の割り算
-- NG：整数同士の割り算は、小数点以下が切り捨てられる
SELECT
1 / 3 AS RATIO;
-- RATIO
-- -------
-- 0
-- OK：どちらかを小数にしてから割る
SELECT
ROUND(1.0 * 1 / 3, 3) AS RATIO
, ROUND(100.0 * 1 / 3, 1) AS RATIO_PCT;
-- RATIO | RATIO_PCT
-- -------+-----------
-- 0.333 | 33.3
整数型を持つPostgreSQL、SQL Server、MySQLなどで起きます。Oracleは例外 で、NUMBER型の割り算が整数で打ち切られることはなく、1 / 3 は 0.3333... を返します。比率や単価を計算する式は、製品を移るときに特に注意が必要です。
落とし穴3：スカラサブクエリの件数
スカラサブクエリは、0件ならNULLを返し、2件以上ならエラーになります。テストデータでは1件しかなくても、本番では2件以上になってエラー、ということがあります。
-- NG：電話番号を1つ取るつもりが、2つ登録されている顧客がいてエラーになる
WITH T_CUSTOMER_TEL(CUSTOMER_ID, TEL_NO) AS (VALUES (1, '03-0000-0001'), (1, '090-0000-0002'))
SELECT
(SELECT TEL_NO FROM T_CUSTOMER_TEL WHERE CUSTOMER_ID = 1) AS TEL_NO;
-- ERROR: more than one row returned by a subquery used as an expression
-- OK：どれを採るかを仕様で決め、並べ替えて1行に絞る
WITH T_CUSTOMER_TEL(CUSTOMER_ID, TEL_SEQ, TEL_NO) AS (VALUES (1, 1, '03-0000-0001'), (1, 2, '090-0000-0002'))
SELECT
(SELECT TEL_NO FROM T_CUSTOMER_TEL WHERE CUSTOMER_ID = 1 ORDER BY TEL_SEQ FETCH FIRST 1 ROW ONLY) AS TEL_NO;
-- TEL_NO
-- --------------
-- 03-0000-0001
FETCH FIRST n ROWS ONLY はSQL標準の書き方で、PostgreSQLでもOracle 12c以降でも使えます。ここでも並べ替えの列が一意でないと、どの行が返るか不定になります。
落とし穴4：CASE式の漏れとNULL判定
CASE式は、どのWHENにも当たらずELSEもないと NULL を返します。また、WHEN STATUS_CD = NULL は常にUNKNOWNなので、絶対に当たりません。
-- NG：ELSEがないので想定外のコードがNULLになり、= NULL の判定は永遠に当たらない
WITH T_ORDER(STATUS_CD) AS (VALUES ('01'), ('02'), ('09'), (NULL))
SELECT
STATUS_CD
, CASE STATUS_CD
WHEN '01' THEN '受付'
WHEN '02' THEN '出荷'
END AS STATUS_NM
, CASE
WHEN STATUS_CD = NULL
THEN '未設定'
ELSE '設定済'
END AS SET_NM
FROM
T_ORDER;
-- STATUS_CD | STATUS_NM | SET_NM
-- -----------+-----------+--------
-- 01 | 受付 | 設定済
-- 02 | 出荷 | 設定済
-- 09 | NULL | 設定済 ← 想定外のコードが黙ってNULLになる
-- NULL | NULL | 設定済 ← NULLなのに「設定済」
-- OK：ELSEで受け止め、NULLは IS NULL で判定する
WITH T_ORDER(STATUS_CD) AS (VALUES ('01'), ('02'), ('09'), (NULL))
SELECT
STATUS_CD
, CASE STATUS_CD
WHEN '01' THEN '受付'
WHEN '02' THEN '出荷'
ELSE '不明'
END AS STATUS_NM
, CASE
WHEN STATUS_CD IS NULL
THEN '未設定'
ELSE '設定済'
END AS SET_NM
FROM
T_ORDER;
-- STATUS_CD | STATUS_NM | SET_NM
-- -----------+-----------+--------
-- 01 | 受付 | 設定済
-- 02 | 出荷 | 設定済
-- 09 | 不明 | 設定済 ← 想定外だと気づける
-- NULL | 不明 | 未設定
ELSEに「不明」を入れておくと、コード値が増えたときに画面や帳票で気づけます。逆にELSEなしでNULLにすると、見た目は空欄なので見落とします。
落とし穴5：DISTINCTで不具合を隠す
結合で行が増えてしまったのを、DISTINCTで消して帳尻を合わせていることがあります。結果が正しく見えても、原因は結合条件の不足かもしれません。
-- NG：結合で増えた行をDISTINCTで消している
WITH T_CUSTOMER(CUSTOMER_ID, CUSTOMER_NM) AS (VALUES (1, 'A'), (2, 'B'))
, T_ORDER(CUSTOMER_ID) AS (VALUES (1), (1))
SELECT DISTINCT
C.CUSTOMER_ID
, C.CUSTOMER_NM
FROM
T_CUSTOMER C
JOIN T_ORDER O
ON O.CUSTOMER_ID = C.CUSTOMER_ID;
-- CUSTOMER_ID | CUSTOMER_NM
-- -------------+-------------
-- 1 | A
-- OK：「注文があるか」を見たいだけなら、そもそも結合しない
WITH T_CUSTOMER(CUSTOMER_ID, CUSTOMER_NM) AS (VALUES (1, 'A'), (2, 'B'))
, T_ORDER(CUSTOMER_ID) AS (VALUES (1), (1))
SELECT
C.CUSTOMER_ID
, C.CUSTOMER_NM
FROM
T_CUSTOMER C
WHERE
EXISTS (SELECT 1 FROM T_ORDER O WHERE O.CUSTOMER_ID = C.CUSTOMER_ID);
-- CUSTOMER_ID | CUSTOMER_NM
-- -------------+-------------
-- 1 | A
今は同じ結果ですが、NG側は後から注文日などの列を足した瞬間に、DISTINCTが効かなくなって行が増えます。
テスト観点
| 観点 | 用意するデータ | 確認すること |
|---|---|---|
| 列の対応 | すべての列が異なる値になる行 | 列の並び・別名が期待どおり |
| 式の計算 | 境界値、0、負数、NULLを含む行 | 計算結果、NULLの伝わり方、丸め・切り捨て |
| 型と桁 | 桁数の大きい値、小数を含む値 | 型変換・桁あふれ・整数除算がないか |
| CASE式 | 各WHENに当たる行、どれにも当たらない行、NULLの行 | ELSEなしでNULLにならないか、= NULL で判定していないか |
| スカラサブクエリ | サブクエリが0件・1件・2件以上になるデータ | 0件でNULL、2件以上でエラーになって困らないか |
| DISTINCT | 重複する行 | 重複が生まれる原因は正当か |
| ウィンドウ関数 | PARTITIONの境界、ORDER BYの同値行 | 区切り・順位・累計が意図どおりか |
ORDER BY と LIMIT
挙動
最終的なワークテーブルを、指定した列で左から順に比較して並べ替えます。指定した列がすべて同じ行どうしの順序は 不定 です。ORDER BYがなければ、行の順序はまったく保証されません6。
落とし穴1：NULLの位置が製品によって違う
PostgreSQLとOracleでは、昇順のときNULLは最後、降順のとき最初に来ます。SQL ServerとMySQLは逆で、昇順のときNULLが最初です。
-- NG：金額の大きい順に並べたら、金額未入力の行が先頭に来た(PostgreSQL / Oracle)
WITH T_ORDER(ORDER_ID, ORDER_AMT) AS (VALUES (10, 2), (11, NULL), (12, 1))
SELECT
ORDER_ID
, ORDER_AMT
FROM
T_ORDER
ORDER BY
ORDER_AMT DESC;
-- ORDER_ID | ORDER_AMT
-- ----------+-----------
-- 11 | NULL ← 先頭
-- 10 | 2
-- 12 | 1
-- OK：NULLの位置を明示する
WITH T_ORDER(ORDER_ID, ORDER_AMT) AS (VALUES (10, 2), (11, NULL), (12, 1))
SELECT
ORDER_ID
, ORDER_AMT
FROM
T_ORDER
ORDER BY
ORDER_AMT DESC NULLS LAST;
-- ORDER_ID | ORDER_AMT
-- ----------+-----------
-- 10 | 2
-- 12 | 1
-- 11 | NULL
NULLS FIRST / NULLS LAST はSQL標準で、PostgreSQLとOracleで使えます。SQL ServerとMySQLでは使えないので、ORDER BY CASE WHEN ORDER_AMT IS NULL THEN 1 ELSE 0 END, ORDER_AMT DESC のように書きます。
落とし穴2：同値の行とLIMITでページングが壊れる
同値がありうる列だけでページングすると、ページをまたいで行が重複したり、抜けたりします。
-- NG：注文日だけで並べる。同じ日の3件(10,11,12)の順序が不定なので、
-- 2ページ目に1ページ目と同じ行が出たり、どこにも出ない行ができたりする
-- 順序不定のはずですが、WITH句を指定してテストすると結果がOKと同じ結果になってしまうので、ここには敢えてWITH句を書いていません。
SELECT
ORDER_ID
FROM
T_ORDER
ORDER BY
ORDER_DT OFFSET 1 FETCH FIRST 2 ROWS ONLY;
-- OK：主キーを最後に足して、順序を一意に確定させる
WITH T_ORDER(ORDER_ID, ORDER_DT) AS (VALUES (10, DATE '2026-09-01'), (11, DATE '2026-09-01'), (12, DATE '2026-09-01'), (13, DATE '2026-09-02'))
SELECT
ORDER_ID
FROM
T_ORDER
ORDER BY
ORDER_DT
, ORDER_ID OFFSET 1 FETCH FIRST 2 ROWS ONLY;
-- ORDER_ID
-- ----------
-- 11
-- 12
落とし穴3：コードを文字列として並べる
-- NG：文字列型のコードは、数値の順には並ばない
WITH T_ITEM(ITEM_CD) AS (VALUES ('10'), ('9'), ('100'))
SELECT
ITEM_CD
FROM
T_ITEM
ORDER BY
ITEM_CD;
-- ITEM_CD
-- ---------
-- 10
-- 100
-- 9
-- OK：桁を揃えてから並べる
WITH T_ITEM(ITEM_CD) AS (VALUES ('10'), ('9'), ('100'))
SELECT
ITEM_CD
FROM
T_ITEM
ORDER BY
LPAD(ITEM_CD, 3, '0');
-- ITEM_CD
-- ---------
-- 9
-- 10
-- 100
そもそもコードをゼロ埋めして保存しておけば、この問題は起きません。並べ替えのたびに式を書くのは、インデックスも効きにくくなります。データ側で揃えるのが本筋 です。
テスト観点
| 観点 | 用意するデータ | 確認すること |
|---|---|---|
| 昇順・降順 | 並べ替え列の値が異なる行(2列なら組み合わせ) | 各列の昇順・降順が意図どおり |
| NULLの位置 | 並べ替え列がNULLの行 | NULLが最初か最後か |
| 同値の行 | 並べ替え列が同じ値の行 | 順序が確定しているか(一意な列で締めているか) |
| ページング | ページ境界に同値の行がまたがるデータ | 行の重複・欠落がないか |
| 文字列の順序 | 桁数の違うコード、大文字小文字、全角半角 | 照合順序が仕様どおりか |
テストのしやすさ：ORDER BYの入力は「SELECT句の結果」です。並べ替えだけを確かめたいなら、SELECT後をイメージしたテーブルに同じORDER BYを当てればテストできます。
句をまたぐ観点
句ごとの観点だけでは拾いきれないものもあります。
| 観点 | 確認すること |
|---|---|
| 0件の入力 | 元テーブルが空、または条件に合う行が1件もないとき、エラーにならず意図どおりの結果になるか |
| UNION と UNION ALL | 重複を消したいのか残したいのか。UNIONは結果の重複行も消すので、意図せず件数が減ることがある |
| 句の組み合わせ | 結合→集約→HAVINGのように、前の句の結果が次の句の前提を崩していないか |
| 本番規模のデータ | 件数・合計値が業務的に妥当か。テストデータでは起きない重複やNULLがないか |
全部はやらない：観点の選び方
ここまでの観点をすべてテストするのは現実的ではありません。次の順で絞り込みます。
1. 制約で消せる観点は消す
まずテーブル定義を確認します。制約があれば、そもそも起きない観点は外せます。
| 制約 | 外せる観点 |
|---|---|
| NOT NULL | その列がNULLになる観点(結合キー、否定条件、GROUP BY、ORDER BY) |
| UNIQUE / 主キー | その列で結合したときの行の増殖、並べ替えの同値 |
| 外部キー | 子テーブル側の「相手がいない行」 |
逆に言うと、NULLを許す列や一意でない列が多いほど、テストで確かめることが増えます。制約をきちんと付けるのが、一番安上がりな品質対策 です。
2. リスクの高い書き方を優先する
SQLを読んで、次の書き方があれば優先的に観点を拾います。
- 外部結合(LEFT / RIGHT / FULL JOIN)
- 1対多の結合をした後の集約
- NULLを許す列に対する
<>、NOT IN、NOT LIKE - 日時の範囲指定
- LIMIT / OFFSET によるページング
- スカラサブクエリ
3. 仕様が決まっていないところは、テストより先に確認する
「NULLの顧客は一覧に出す？」「同額のときの並び順は？」のように、観点を拾っていくと仕様が決まっていないことに気づきます。これはテストで確かめるものではなく、仕様として決めるものです。観点の一覧は、仕様の穴を見つける道具にもなります。
テストしやすいSELECT文の書き方
同じ結果を返すSELECT文でも、書き方によってテストのしやすさは大きく変わります。レビューでは、正しさと合わせて「テストしやすいか」も見るのがおすすめです。
1. 前の句の結果をイメージしたテーブルで、句を取り出してテストする
前提1で書いたとおり、各句の入力は前の句の結果だけです。なので、前の句の結果をイメージしたテーブルを用意すれば、その句だけをテストできます。
| テストしたい句 | 用意するテーブル | うれしいこと |
|---|---|---|
| WHERE | 結合後のイメージ | 複数テーブルのデータを揃えずに、検索条件だけ確かめられる |
| ORDER BY | SELECT後のイメージ | 並べ替えだけを確かめられる |
ただし、句そのものを書き換えないことが条件 です。たとえばHAVINGを、集約後のテーブルに対するWHEREに置き換えてしまうと、テストしているのは元のSQLとは別物になります。この表に挙げたのは、句をそのままの形で別の入力に当てられるものだけです。
2. WITH句で段階に分けて書く
長いSELECT文は、論理的な評価順序に沿ってWITH句(CTE)で段階に分けて書くと、途中の段階の結果を単独で確認 できます。2019年版で書いた「句ごとにSQLを分割してテストする」を、1本のSQLのまま実現する方法です。
-- 段階1：対象の注文を絞る
WITH W_TARGET AS (SELECT CUSTOMER_ID, ORDER_AMT FROM T_ORDER WHERE ORDER_TS >= '2026-09-01' AND ORDER_TS < '2026-10-01')
,
-- 段階2：顧客ごとに集約する
W_SUM AS (SELECT CUSTOMER_ID, SUM(ORDER_AMT) AS TOTAL_AMT FROM W_TARGET GROUP BY CUSTOMER_ID)
-- 段階3：条件で絞って並べる
SELECT
CUSTOMER_ID
, TOTAL_AMT
FROM
W_SUM
WHERE
TOTAL_AMT >= 1000
ORDER BY
TOTAL_AMT DESC
, CUSTOMER_ID;
最後のSELECTを SELECT * FROM W_TARGET に差し替えれば段階1の結果を、SELECT * FROM W_SUM にすれば段階2の結果を確認できます。段階3は、W_SUM をイメージしたテストデータに差し替えればテストできます。
(中間のCTEは、この記事では便宜上 W_ で始めています)
3. WITH句でテーブルを差し替える
WITH句で定義した名前は、同じ名前のテーブルより優先されます。これを利用すると、本番のSQLを1文字も変えずに、先頭にWITH句を足すだけ でテストデータに差し替えられます。
-- 本番のSQLの先頭に、テストデータのWITH句を足すだけ
WITH T_CUSTOMER(CUSTOMER_ID, CUSTOMER_NM) AS (VALUES (1, '通常'), (2, '注文なし'))
, T_ORDER(ORDER_ID, CUSTOMER_ID, ORDER_AMT) AS (VALUES (10, 1, 100))
-- ↓ ここから下は本番のSQLそのまま
SELECT
C.CUSTOMER_ID
, COUNT(O.ORDER_ID) AS ORDER_CNT
FROM
T_CUSTOMER C
LEFT JOIN T_ORDER O
ON O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY
C.CUSTOMER_ID;
テーブルを作ったり、データを投入・削除したりする必要がなく、共有のテスト環境のデータも汚しません。本番のSQLがすでにWITH句を使っている場合は、その先頭にテストデータの定義を足します。PostgreSQLで確認していますが、お使いのRDBMSでも同じように動くか確かめてください。
4. 現在日時をSQLに埋め込まない
CURRENT_DATE や SYSDATE を直接書くと、実行する日によって結果が変わり、期待値を固定できません。基準日はバインド変数やパラメータで渡します。
-- テストしにくい：実行日によって結果が変わる
WHERE ORDER_TS >= CURRENT_DATE - 30
-- テストしやすい：基準日を外から渡す
WHERE ORDER_TS >= :BASE_DT - 30
月末・年末・うるう年など、日付の境界のテストもしやすくなります。乱数や、シーケンスの採番値なども同じ考え方です。
5. 順序を確定させる
ORDER BYで一意な列まで指定しておくと、実行するたびに行の順序が変わることがなく、期待値と行単位でそのまま比較できます。順序が不定だと、テストが通ったり落ちたりする不安定なテストになります。
6. SELECT * を使わない
SELECT * は、テーブルに列が追加されると結果が変わります。列を明示しておけば、テーブル定義の変更でテストが壊れることがなく、SELECT句の観点(列の並び・別名)も確かめやすくなります。
7. ロジックをビューに切り出しすぎない・詰め込みすぎない
何度も使う結合や集約は、ビュー(V_)に切り出すと、ビュー単体でテストできます。一方で、ビューの上にビューを重ねすぎると、どの段階で問題が起きたか追いにくくなり、性能も読みにくくなります。1本のSQLに詰め込みすぎないことと、部品を重ねすぎないことのバランスを取ります。
テストデータと期待値の作り方
意地悪なデータを混ぜる
2019年版では、長いSELECT文を句ごとに分割してテストすることを勧めていましたが、自分で書いていてなんですが、メンドクサイです。
なので、クエリは1本のまま、テストデータに意地悪な行を混ぜておく 方式に落ち着いてきました。前の章の「WITH句でテーブルを差し替える」と組み合わせると、テーブルを作らずにデータを用意できます。
WITH T_CUSTOMER(CUSTOMER_ID, CUSTOMER_NM) AS (VALUES
(1, '通常'),
(2, '注文なし'), -- 片側だけにある行
(3, '注文複数')
),
T_ORDER(ORDER_ID, CUSTOMER_ID, ORDER_AMT) AS (VALUES
(10, 1, 100),
(11, 3, 200),
(12, 3, 300), -- 結合で行が増える
(13, NULL, 400), -- 結合キーがNULL
(14, 3, NULL) -- 集約対象がNULL
)
SELECT
C.CUSTOMER_ID
, C.CUSTOMER_NM
, COUNT(O.ORDER_ID) AS ORDER_CNT
, COALESCE(SUM(O.ORDER_AMT), 0) AS TOTAL_AMT
FROM
T_CUSTOMER C
LEFT JOIN T_ORDER O
ON O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY
C.CUSTOMER_ID
, C.CUSTOMER_NM
ORDER BY
C.CUSTOMER_ID;
-- CUSTOMER_ID | CUSTOMER_NM | ORDER_CNT | TOTAL_AMT
-- -------------+-------------+-----------+-----------
-- 1 | 通常 | 1 | 100
-- 2 | 注文なし | 0 | 0
-- 3 | 注文複数 | 3 | 500
1回の実行で、片側だけの行・行の増殖・NULLキー・NULL集約をまとめて確かめられます。
期待値は、SQLを見ずに作る
期待値をSQLの実行結果から貼り付けると、「SQLが正しいことを、そのSQLで確かめる」ことになり、意味がありません。期待値は、仕様とテストデータから手で作ります。
書き換え前後のSQLを突き合わせる
リファクタリングや性能改善でSQLを書き換えるときは、新旧のSQLの結果を突き合わせると、期待値を手で作らずに済みます。EXCEPTは片方向の差しか出さないので、両方向 で確認します。
-- どちらも0件なら、結果は一致
(旧SQL) EXCEPT ALL (新SQL);
(新SQL) EXCEPT ALL (旧SQL);
実際に当てはめてみます。FROM / JOINの節で出てきた「IN を JOIN に書き換えたら行が増えた」を題材にします。新旧のSQLをWITH句に入れて、1回の実行で両方向の差を出します。
WITH T_ITEM(ITEM_CD) AS (VALUES ('001'), ('002'))
, T_ORDER(ITEM_CD) AS (VALUES ('001'), ('001'))
,
-- 品目001の注文が2件ある
-- 旧SQL：注文がある品目を取る
W_OLD AS (SELECT ITEM_CD FROM T_ITEM WHERE ITEM_CD IN (SELECT ITEM_CD FROM T_ORDER))
,
-- 新SQL：性能改善のつもりでJOINに書き換えた
W_NEW AS (SELECT I.ITEM_CD FROM T_ITEM I JOIN T_ORDER O ON O.ITEM_CD = I.ITEM_CD)
SELECT
'旧にしかない' AS DIFF_NM
, ITEM_CD
FROM
(SELECT * FROM W_OLD EXCEPT ALL SELECT * FROM W_NEW) D1
UNION ALL
SELECT
'新にしかない'
, ITEM_CD
FROM
(SELECT * FROM W_NEW EXCEPT ALL SELECT * FROM W_OLD) D2;
-- DIFF_NM | ITEM_CD
-- --------------+---------
-- 新にしかない | 001 ← 新SQLだけ001が1行多い
1行でも返ってきたら、新旧で結果が違うということです。どちらの向きに差があるかも同時に分かります。
EXCEPT ではなく EXCEPT ALL を使うのが肝心です7。上のSQLの EXCEPT ALL を EXCEPT に変えると、重複が取り除かれてしまい、0件になって差を見逃します。
-- 同じデータで EXCEPT (ALLなし) にすると…
WITH T_ITEM(ITEM_CD) AS (VALUES ('001'), ('002'))
, T_ORDER(ITEM_CD) AS (VALUES ('001'), ('001'))
,
W_OLD AS (SELECT ITEM_CD FROM T_ITEM WHERE ITEM_CD IN (SELECT ITEM_CD FROM T_ORDER))
,
W_NEW AS (SELECT I.ITEM_CD FROM T_ITEM I JOIN T_ORDER O ON O.ITEM_CD = I.ITEM_CD)
SELECT
'旧にしかない' AS DIFF_NM
, ITEM_CD
FROM
-- EXCEPT (ALLなし)
(SELECT * FROM W_OLD EXCEPT SELECT * FROM W_NEW) D1
UNION ALL
SELECT
'新にしかない'
, ITEM_CD
FROM
-- EXCEPT (ALLなし)
(SELECT * FROM W_NEW EXCEPT SELECT * FROM W_OLD) D2;
-- (0 rows) ← 行が増えているのに「一致」と判定してしまう
なお、突き合わせで確かめられるのは「新旧で結果が同じこと」だけです。旧SQLにもともとあった不具合は見つかりません。
テストの進め方
ここまでの内容を、実際にSELECT文をテストするときの手順にまとめます。
- 情報を集める：RDBMSの製品とバージョン、テーブル定義(NULL許容・主キー・一意・外部キー)、SQLの仕様(何を取りたいのか)を確認します。
- SQLを読んで、リスクの高い書き方を探す：外部結合、1対多の結合の後の集約、否定条件、日時の範囲、ページング、スカラサブクエリ、CASE式など。
- 観点を選ぶ：句ごとの観点の表から当てはまるものを拾い、制約で起きないものは外します。
- 仕様の穴を確認する：「NULLのときは？」「同値のときの並び順は？」など、決まっていないことはテストの前に確認します。
- テストデータを作る：WITH句でテーブルを差し替え、意地悪な行を混ぜます。各行に、どの観点のための行かをコメントで書いておきます。
- 期待値を作る：SQLを見ずに、仕様とテストデータから作ります。
- 実行して比べる：順序を確定させて、期待値と行単位で比べます。
- 結果をまとめる：観点ごとに、データ・期待値・結果を残します。テストしなかった観点と、その理由(制約で起きない、対象外など)も書いておきます。
結果は、たとえば次のような表にまとめます。
| 観点 | テストデータ | 期待値 | 結果 |
|---|---|---|---|
| 片側だけにある行 | 顧客2(注文なし) | ORDER_CNT=0、TOTAL_AMT=0で出力される | OK |
| 行の増殖 | 顧客3(注文3件) | TOTAL_AMT=500(二重計上なし) | OK |
| 結合キーのNULL | 注文13(CUSTOMER_IDがNULL) | どの顧客にも集計されない | OK |
| 集約対象のNULL | 注文14(ORDER_AMTがNULL) | 件数には含み、合計には含まない | 仕様確認中 |
| GROUP BY列のNULL | ー | ー | 対象外(CUSTOMER_IDは主キー) |
「何をテストしたか」だけでなく「何をテストしなかったか」が残っていると、レビューする人も、後でSQLを直す人も助かります。
まとめ
- SELECT文のバグの多くは、挙動の思い込みから生まれる。まず論理的な評価順序と3値論理を押さえる
- テスト観点は、全部やるためのものではなく、SQLを読んで当てはまるものを拾うためのもの
- NOT NULL・UNIQUE・外部キーなどの制約で、観点そのものを減らせる
- 各句の入力は前の句の結果だけ。前の句の結果をイメージしたテーブルがあれば、句を取り出してテストできる
- WITH句で段階に分ける、テーブルを差し替える、現在日時を埋め込まないなど、テストしやすい書き方を選ぶ
- 期待値は仕様から作る。書き換えなら新旧の結果を両方向に突き合わせる
参考文献
- Joe Celko 著、ミック 監訳『プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに』翔泳社(SELECT文の評価順序と挙動、3値論理)
- PostgreSQL 文書：SELECT
- Oracle Database SQL言語リファレンス：SELECT
- 図でイメージするOracleのSQL全集 第1回 さまざまな結合(初版で参照したOracle公式サイトの記事がリンク切れになってました。明智重蔵氏のページにあったので参考まで。Oracle独自のSTART WITH句やMODEL句を含む評価順序は、ここで読めます)
- Microsoft：SELECT (Transact-SQL)
- ja.stackoverflow：ON句がJOIN句よりも先に評価されるのは本当ですか？
- Wikipedia：Select (SQL)
改訂履歴
- 2019-06-14：初版「SQLの単体テストケースって、どうやって考えるの？」
- 2024-10-21：軽微な修正
-
2026-09-26：全面改訂
- 観点を句ごとに表形式で整理し、落とし穴の実例SQLを「NG / OK」の対で追加(PostgreSQL 16で実行して確認)
- 行の増殖、外部結合のINNER化、INとJOINの違い、NOT INとNULL、日時の終端、型の暗黙変換、末尾空白、0件の集約、整数除算、CASE式、スカラサブクエリ、NULLの並び順、ページングを追加
- 評価順序にDISTINCT、集合演算、LIMITを追加
- 「観点の選び方」「テストしやすいSELECT文の書き方」「テストデータと期待値の作り方」「テストの進め方」を追加
- 句ごとにSQLを分割する方法をやめ、WITH句で段階に分ける方法と、意地悪なデータを混ぜる方法に変更
-
SQL標準や各RDBMSのマニュアルで、細かい順序や名前は少しずつ違います。たとえばOracleには START WITH / CONNECT BY / MODEL、SQL Serverには TOP などの独自の句があります。 ↩
-
PostgreSQLやMySQLは、SELECT句の別名をGROUP BYやORDER BYで使えますが、WHEREでは使えません。 ↩
-
巨大になるので、実際にクロス結合を行う製品はありません。あくまで挙動を理解するための考え方です。 ↩
-
GROUP BYがなくてもHAVINGは使えます。 ↩
-
「余分な」重複行を削除する、つまり1行は残ります。ALLを指定した場合や、ALLもDISTINCTもない場合は、重複行もすべて残ります。 ↩
-
ORDER BYに、SELECT句に現れない列や式を指定することもできます(DISTINCTがある場合を除く)。 ↩
-
EXCEPT ALL は PostgreSQL などで使えます。Oracleでは 21c から使えます。使えない製品では、GROUP BY で件数を集計してから比較します。 ↩