高度なSQL構文とクエリ最適化（相関副問合せ・Window関数・EXISTS）

SQLを改善するときは、先に結果の意味を確認します。副問合せをJOINへ変えたら行が増えた、NULLのあるデータで未注文顧客が消えた、同点の順位が意図と違った、という誤りは速度より先に修正すべき問題です。実際の実行方法はオプティマイザが決めるため、構文だけで処理量を断定しません。

論理処理と実行計画を区別する

概念的にはFROM・JOINで対象を作り、WHEREで行を絞り、GROUP BYで集約し、HAVINGでグループを絞り、SELECTの出力や分析関数を評価し、DISTINCT、ORDER BY、LIMITなどを適用します。分析関数の対象はWHEREや集約後の行であり、元表の全行とは限りません。これは意味を理解するための順序で、物理的にこの順に表を走査するという指定ではありません。

SELECTで付けた別名をWHEREやHAVINGで使えるかは製品差があります。PostgreSQLではその使い方はできません。順位で絞るときは、分析関数を含む問合せをCTEや導出表に置き、外側のWHEREで順位を参照します。

相関副問合せは、結果を保って比較する

SELECT e.社員番号, e.給与
FROM 社員 e
WHERE e.給与 > (
  SELECT AVG(s.給与) FROM 社員 s
  WHERE s.部署コード = e.部署コード
);

これは社員ごとに所属部署の平均給与と比較する意味です。物理的に毎回全表走査するとは限らず、索引や内部書換え、実行結果の再利用で評価される場合があります。部署別平均を作ってJOINする形とも比較できますが、NULLの部署、複数一致による行の重複、集約の単位を変えないようにします。

EXISTSは条件を満たす行が存在するかを判定し、通常のJOINのように一致行数分だけ外側の行を増やしません。存在確認をJOINへ置き換えるときは、右側の一意性や重複排除の必要性を確認します。構文を変えれば必ずO(N+M)や最速になる、という保証はありません。

NOT INとNULLの3値論理

比較相手の集合にNULLがあると、一致しない値に対するNOT INはUNKNOWNになります。一致する値はFALSEです。「NULLがあれば全行の条件がUNKNOWNになる」わけではありませんが、WHEREが採用するTRUEがなくなり、この例では結果が0件になります。副問合せが空集合ならNULLとの比較を含む非空集合とは挙動が違うため、空集合も試します。

SELECT c.顧客ID
FROM 顧客 c
WHERE NOT EXISTS (
  SELECT 1 FROM 注文 o WHERE o.顧客ID = c.顧客ID
)
ORDER BY c.顧客ID;

非NULLの顧客IDを持つ顧客に対して、この条件は一致する注文がないことを確認します。注文のNULL行は等値条件に一致しません。EXISTS自身は行の存在を判定するのでSELECT NULLでも行があればTRUEです。外側の値がNULLの場合の意味まで含めて、NOT INと常に等価だと思わないようにします。

分析関数は、同点とフレームを決める

WITH ranked AS (
  SELECT 社員番号, 部署コード, 給与,
    ROW_NUMBER() OVER (
      PARTITION BY 部署コード
      ORDER BY 給与 DESC, 社員番号
    ) AS rn
  FROM 社員
)
SELECT 社員番号, 部署コード, 給与
FROM ranked WHERE rn = 1
ORDER BY 部署コード;

ROW_NUMBERは同点でも別の連番を付けます。給与だけで順序を指定すると同点の選ばれ方が安定しないため、例では社員番号を第2キーにしています。同点の全員を1位として取得したいなら、給与だけを基準とするRANKやDENSE_RANKを使ってrank=1を抽出します。

累積和ではSUM(金額) OVER (ORDER BY 日時,番号 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)のように、順序とフレームを明示します。ORDER BYだけで指定した既定フレームでは、同じ順序値の行がまとめて含まれる場合があります。分析関数は可読性を改善しますが、ソートや一時領域が不要になるとは限りません。

演習1：未注文顧客の抽出

条件：顧客は1・2・3。注文の顧客IDは1・1・NULL。顧客IDは非NULL。

問い：NOT INと相関NOT EXISTSの結果を比べよう。

解答例：NOT INは0件。NOT EXISTSは顧客2と3。

根拠：NOT INでは1がFALSE、2と3がUNKNOWN。NOT EXISTSは一致注文のない顧客を返す。

演習2：同点で1人を選ぶ

条件：部署内の最大給与の社員が2人いるが、1人だけ安定して選びたい。

問い：ROW_NUMBERに必要な順序条件を説明しよう。（35字以内）

解答例：給与の降順に加え一意な社員番号で順序を決める。（23字）

根拠：同点の選択条件を固定する。全員を返す要件ならRANKなどを検討する。

復習で確かめること

例の数値や業務条件を変えて同じ結論になるか確認してください。用語の定義だけでなく、問題文のどの条件から、どの制約・SQL・対策を選んだのかを自分の言葉で説明できれば、次の過去問に進みます。

出典と仕様を確認する

IPA：DBシラバス Ver.4.1

PostgreSQL 18：SELECT

PostgreSQL 18：副問合せとNULL

PostgreSQL 18：分析関数

関連するテーマ

SQL結合アルゴリズム（Nested Loops・Hash・Sort Merge）と実行計画

トランザクション分離レベルとMVCC（多版型同時実行制御）の内部動作