SQLの結合と集計｜JOIN・NULL・副問合せを結果表から理解する

SQLを読めるようになるには、句の名前を暗記するだけでは足りません。結合後に何行になり、条件でどの行が残り、集計が何を数えるかを小さな表で追います。ここでは、未注文の顧客を含めた一覧と受注額の集計を、一つの業務データから作ります。

この記事で理解すること

• JOINで行が増える条件と、LEFT JOINで未対応行を残す仕組みを説明する。

• WHERE・GROUP BY・HAVINGの役割と、COUNT・SUM・NULLの違いを理解する。

• EXISTS・副問合せ・集合演算を、必要な結果の条件から選ぶ。

事例は架空の文具卸会社です。表の構造とキーの詳しい意味はE-R図の記事にまとめます。ここでは顧客ID・注文ID・商品IDを整数で表し、数量は正、受注単価は0以上でNULLを許さない条件とします。以下のSQLは基本的な標準SQLの範囲を中心に使います。

最初にデータと、数えたい一件を固定する

顧客は3件、注文は3件、明細は4件です。青葉商店は2注文で600円、みなと社は1注文で600円、山川社は注文がありません。税や値引き・丸めを含めず、明細金額＝数量×受注単価とします。顧客数・注文数・明細数のどれを数えるかを最初に決めます。

INNER JOIN：一致した組だけを作る

内部結合は、結合条件に一致する行の組を結果へ残します。顧客と注文を顧客IDで結ぶと、青葉商店は注文201と202の2行、みなと社は203の1行になり、山川社は残りません。注文を持たないからです。

SELECT c.顧客ID, c.顧客名, o.注文ID
FROM 顧客 c
JOIN 注文 o ON o.顧客ID = c.顧客ID;

さらに明細へ結合すると、注文201は2行になります。JOINは別表の項目を横へ付けるだけの処理ではなく、複数の一致があれば行数を増やします。注文IDを単純にCOUNTすると、この例では明細数を数えてしまいます。必要な粒度へ先にまとめるか、COUNT(DISTINCT 注文ID)等を検討します。

結合条件を書き忘れたCROSS JOINなどは、各表の全行の組を作ります。顧客3件と注文3件なら9行です。別の表同士で、たまたま数値が同じだからという理由でキーを結ぶのも誤りです。参照関係に対応する列を選びます。

LEFT JOIN：未対応の左側を残す

左外部結合は、一致する右側の行があればその組を作り、なければ左側の行を残して右側の項目をNULLにします。顧客を左側にすれば、未注文の山川社も一行残ります。NULLはここでは「対応する注文行がない」ことを表し、実在する注文IDが0という意味ではありません。

SELECT c.顧客ID, c.顧客名, o.注文ID
FROM 顧客 c
LEFT JOIN 注文 o ON o.顧客ID = c.顧客ID;

左側が常に一行だけ残るわけではありません。一致する注文が複数あれば顧客行は複数になります。「LEFT JOINなら顧客数と同じ行数」という説明は成り立ちません。

ONとWHERE：外部結合では条件の場所が結果を変える

ONは結合で組を作る条件、WHEREは結合後の行を残す条件です。未対応行の注文IDはNULLなので、WHERE o.注文ID = 202を付けると、山川社の行は残りません。特定の注文だけを対応させつつ全顧客を残すなら、条件をON側へ置きます。

SELECT c.顧客ID, o.注文ID
FROM 顧客 c
LEFT JOIN 注文 o
  ON o.顧客ID = c.顧客ID
 AND o.注文ID = 202;

このSQLは顧客101に注文202を結び、102と103は注文IDがNULLの行として残します。一方WHERE c.顧客ID = 101は、左側の対象顧客を絞る条件です。条件を全てONへ移せばよいわけではなく、残す対象と対応させる対象を分けて考えます。

NULLと三値論理：不明は真でも偽でもない

NULLは値がない・不明・適用できない等を表す印で、0や空文字と同じではありません。通常の比較でNULL = NULLやNULL <> 0を評価しても真にはならず、未知という扱いになります。WHEREは真の行を残すため、未知の行も除かれます。

NULLかどうかはIS NULL、NULLでないかはIS NOT NULLで調べます。COALESCEは最初のNULLでない値を選ぶ関数です。集計結果がNULLなら表示を0へ置き換える用途がありますが、業務で不明な金額を勝手に0とみなしてよいかは別に判断します。

この例のLEFT JOINを顧客ごとにまとめると、山川社のCOUNT(*)は1、COUNT(o.注文ID)は0です。注文IDは実在する注文ではNULLにならないキーなので、外部結合で対応する注文の数を数える用途に使えます。

WHERE・GROUP BY・HAVING：行の条件と集約後の条件

結果を考えるときは、FROMとJOINで組を作り、WHEREで行を絞り、GROUP BYでグループ化し、HAVINGでグループを絞り、SELECTで出力し、ORDER BYで並べる、という論理的な順序で追います。DBの物理的な実行順序を固定する説明ではありません。

SELECT c.顧客ID, c.顧客名,
       SUM(d.数量 * d.受注単価) AS 受注額
FROM 顧客 c
JOIN 注文 o ON o.顧客ID = c.顧客ID
JOIN 注文明細 d ON d.注文ID = o.注文ID
GROUP BY c.顧客ID, c.顧客名
HAVING SUM(d.数量 * d.受注単価) >= 600
ORDER BY c.顧客ID;

明細ごとの金額を合計すると、顧客101と102がともに600円で残ります。顧客名が同じ別顧客を一緒に集計しないよう、顧客IDを使います。選択した非集約列をGROUP BYへ含める書き方は、DB製品の機能差による誤解を避けやすい形です。

ある期間の注文だけを集計するなら注文日の条件はWHEREへ置き、期間内の合計が一定以上という条件はHAVINGへ置きます。集計前に行を消す条件と、集計値を見てグループを消す条件では、合計する対象が違います。

未注文も0円で表示する

全顧客を残すには、顧客から注文、注文から明細の両方をLEFT JOINにします。後段をINNER JOINにすると、明細へ一致しない未注文顧客を消すことがあります。SUMは対象の非NULL値がない場合NULLになるため、この業務で0円表示が適切ならCOALESCEで置き換えます。

SELECT c.顧客ID,
       COALESCE(SUM(d.数量 * d.受注単価), 0) AS 受注額
FROM 顧客 c
LEFT JOIN 注文 o ON o.顧客ID = c.顧客ID
LEFT JOIN 注文明細 d ON d.注文ID = o.注文ID
GROUP BY c.顧客ID
ORDER BY c.顧客ID;

結果は101が600、102が600、103が0です。SUM(DISTINCT 金額)で結合による重複を直そうとすると、別の明細が同じ金額である場合まで一つにしてしまいます。重複の原因を調べ、表や集計の粒度を調整することが先です。

副問合せ・EXISTS・NOT IN：存在と値の比較を区別する

副問合せは別の問合せをSQLの一部に使う仕組みです。EXISTSは副問合せの結果が一行でもあるかを判定します。次の相関副問合せは、外側の顧客ごとに注文の存在を条件として表します。SELECT 1の数値自体を注文数として使うわけではありません。

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

この結果は101と102です。注文が二つある101も、外側の顧客行は一つだけ残ります。注文のない顧客なら同じ条件にNOT EXISTSを使い、103を得ます。JOINで行を増やしてからDISTINCTする方法との違いを理解します。

INは集合の値との一致を調べます。NOT INの右側にNULLが含まれると、一致しない値についても未知になり、期待した行が残らないことがあります。NULLを含み得る列を使って不在を判定するときは、条件を明示したNOT EXISTSやNULL除外の妥当性を確認します。単一値として使う副問合せは、複数行を返すとエラーになる場合がある点も注意します。

集合演算と並び順

UNIONは二つの問合せ結果を合わせて重複行を除き、UNION ALLは重複を残します。INTERSECTは共通する行、EXCEPTは左側にあり右側にはない行を求めます。対応する列数と型の整合が必要で、製品によって演算の名称・対応が異なる場合があります。

この例で「商品301を注文した顧客」は101、「商品302を注文した顧客」は101と102です。集合として合わせれば101と102、共通なら101、後者から前者を引けば102です。JOINによる組合せと、同じ形の結果表同士の集合演算を区別します。

ORDER BYがなければ出力順序は保証されません。主キー順や挿入順に見える結果を、そのまま業務上の順序として扱わないようにします。ウィンドウ関数、再帰問合せ、索引と実行計画は別記事へ分け、ここでは結果の意味を確定します。

演習1：結合後の注文数

条件：注文と明細を結合した結果は4行。注文201は2明細を持つ。

問い：COUNT(注文ID)は注文件数になるか。

解答例：ならない。注文201を2回数え、このデータでは明細数の4になる。

根拠と誤答の確認：COUNT(DISTINCT 注文ID)なら注文IDの種類数3を数えられます。

演習2：未注文顧客の件数

条件：顧客から注文へLEFT JOINし、顧客ごとに集計する。

問い：山川社のCOUNT(*)とCOUNT(o.注文ID)を答える。

解答例：COUNT(*)は1、COUNT(o.注文ID)は0。補完行は存在するが注文IDはNULLのため。

根拠と誤答の確認：列の非NULL値を数える関数と、結果の行を数える関数を分けます。

演習3：条件の置き場所

条件：注文202だけを対応させつつ、全顧客を一覧へ残したい。

問い：注文IDの条件はONとWHEREのどちらへ置くか。

解答例：LEFT JOINのONへ置く。非対応顧客をNULL補完して残すため。

根拠と誤答の確認：WHERE o.注文ID = 202では未対応行が除かれます。

演習4：集計条件

条件：顧客ごとの受注合計が600円以上のグループを残す。

問い：使う句と、この例の結果を答える。

解答例：HAVINGを使い、顧客101と102が各600円で残る。

根拠と誤答の確認：WHEREに集約関数を書いて集計後の値を選ぶ形とはしません。

演習5：不在の条件

条件：NOT INの副問合せが101とNULLを返す。候補の顧客IDは103。

問い：103が確実に残るか。

解答例：残らない。103のNOT INは未知になり、WHEREで除かれるため。

根拠と誤答の確認：NULLが不明な値であるため「101以外」と同じ条件にはなりません。

演習6：同額の明細

条件：異なる二明細の金額がそれぞれ200円。

問い：SUM(DISTINCT 明細金額)で正しい合計を得られるか。

解答例：得られない。同じ値200を一つにし、400円ではなく200円になる。

根拠と誤答の確認：同額の別取引と、JOINによる同一取引の重複は区別します。

参照資料とこの記事の範囲

事例・図・演習は教材用に独自に作成しました。技術仕様とIPAの公開資料を照合し、特定年度の問題本文を前提にせず学べる構成にしています。

PostgreSQL JOIN

PostgreSQL 表式・GROUP BY・HAVING

PostgreSQL 副問合せとNULL

PostgreSQL 集約関数

PostgreSQL 集合演算

IPA APシラバス

関連テーマを続けて学ぶ

IP・サブネット・経路・NAT｜宛先と変換前後を追って通信を理解する

DNSと名前解決｜FQDN・レコード・キャッシュ・TTLを一つの流れで理解する

E-R図とキー｜業務ルールからエンティティ・関連・多対多を設計する

記述式の設問と本文根拠の読み方