JOINのファンアウトと行の増加
JOINによってどちらのテーブルよりも多くの行が返る理由と、面接での確認方法を学びます。
「JOINのファンアウトと行の増加」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
結合が行を返しすぎる場合
面接でよく出る、単純そうでいて本質的な質問があります。「結合によって、大きい方のテーブルより多くの行が返ることはありますか」という質問です。答えは「はい」で、この現象はファンアウトまたは行の乗算と呼ばれます。
「結合は単にテーブルを組み合わせるだけです」と答える候補者は、この点を見落としています。正確な行数を予測できる候補者は採用されます。このレッスンでは、その予測力を身につけます。
原因: 1対多の一致
ファンアウトは、左側の1行が右側の複数行と一致するときに発生します。一致ごとに別々の出力行が生成されます。
顧客と注文の例では、Adaという1人の顧客に2件の注文があります。結合は注文ごとに1行を出力するため、Adaは重複します。顧客のフィールドは繰り返され、異なるのは注文のフィールドだけです。
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- Ada appears twice (she has 2 orders)
-- name | amount
-- Ada | 50
-- Ada | 20
-- Bob | 99出力行数を数える
出力行数は顧客数ではなく、左側の各行に対する一致数の合計になります。
- Ada -> 2件の注文 -> 2行
- Bob -> 1件の注文 -> 1行
- Cleo -> 0件の注文 -> 0行（INNER JOINで除外）
顧客テーブルも3行ですが、合計は3行です。Adaの注文数を10件に変えると、結果は11行に増えます。
多対多では爆発的に増える
同じキーに対して両側に複数の一致があると、ファンアウトはさらに大きくなります。キーKが左側に3回、右側に4回現れる場合、そのキーについて結合は3 x 4 = 12行を生成します。
このようにして、一見小さな結合が数百万行に膨れ上がることがあります。面接官は、乗算に気付けるかを見るために、両側に重複キーがある問題をよく出します。
-- left has 3 rows with tag 'A', right has 4 rows with tag 'A'
SELECT l.id, r.id
FROM left_t l
JOIN right_t r ON r.tag = l.tag;
-- tag 'A' alone yields 3 * 4 = 12 output rows集計の落とし穴
ここでは、面接官が最もよく仕掛けるバグを紹介します。注文をorder_itemsと結合して明細を取得し、その後で注文金額をSUMします。各注文が複数の明細行にファンアウトするため、注文金額が明細ごとに1回ずつ数えられます。
その結果、SUMは大幅に過大になります。クエリは正しそうに見え、実際に実行もできるため、危険なバグです。
-- BUG: order.amount duplicated across items
SELECT SUM(o.amount) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id;
-- a 3-item order counts o.amount 3 times過大計上を確認する
ある注文の金額が100で、明細が3行あるとします。結合によって3行が生成され、それぞれに金額100が含まれます。SUM(o.amount)は100ではなく300を返します。
正しい粒度で集計することが解決策です。明細を合計するか、注文を別途重複なしで集計してください。子テーブルとのファンアウトした結合をまたいで、親の値を決してSUMしないでください。
o.id | o.amount | i.id
7 | 100 | 71
7 | 100 | 72
7 | 100 | 73
-- SUM(o.amount) = 300 (WRONG, should be 100)解決策1: 子側を先に集計する
最も明快な解決策は、サブクエリまたはCTEで多側を事前集計し、各親が1つの集計済み行だけに一致するようにすることです。ファンアウトも過大計上も発生しません。
ここでは、結合する前に明細を注文ごとに1行へまとめているため、親の金額が重複することはありません。
SELECT o.id, o.amount, i.item_count
FROM orders o
JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) i ON i.order_id = o.id;解決策2: COUNT(DISTINCT)と条件付き集計
ファンアウトした結合の後で集計する必要がある場合は、正しい粒度で数えるか合計してください。明細行ではなく注文を数えるには、COUNT(DISTINCT o.id)を使用します。
注意: SUM(DISTINCT o.amount)は安全な解決策ではありません。異なる2つの注文が同じ金額になることはあり、その場合は1つにまとめられてしまうためです。事前集計の方が信頼性は高くなります。
SELECT COUNT(DISTINCT o.id) AS num_orders,
COUNT(i.id) AS num_items
FROM orders o
JOIN order_items i ON i.order_id = o.id;ファンアウトを早期に検出する
面接官が好む簡単な診断方法があります。「1」の側になると想定している側で、結合キーが一意かどうかを確認してください。重複キー数が行数より少なければ、その側には重複があり、ファンアウトが発生します。
-- if this returns rows, order_id is NOT unique in order_items
SELECT order_id, COUNT(*) AS n
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1;COUNTで粒度を確認する
結合結果に対する集計を信頼する前に、行数が妥当か確認してください。手早い方法は、結合後の行数を、粒度になると想定しているテーブルの行数と比較することです。
結合に対するCOUNT(*)がordersのCOUNT(*)より大きければ、結合でファンアウトが発生しており、注文単位の集計は危険な状態です。この1行のチェックが、多くの面接での回答を救ってきました。
-- joined rows should equal order count if no fan-out
SELECT COUNT(*) AS joined_rows
FROM orders o
JOIN order_items i ON i.order_id = o.id;
SELECT COUNT(*) AS order_rows FROM orders;
-- joined_rows > order_rows => fan-out presentファンアウトは常にバグとは限らない
子側の行を1行ずつ表示したい場合もあります。注文ヘッダーとともにすべての明細行を一覧表示するなら、ファンアウトは正しい動作です。重要なのは、目的の粒度、つまり1つのエンティティから何行を生成すべきかを把握することです。
クエリを書く前に粒度を明確にしてください。「注文アイテムごとに1行」が必要なのか、「注文ごとに1行」が必要なのかによって、ファンアウトが機能になるかバグになるかが決まります。
確認問題
1対多の結合結果を予測してください。
まとめ: ファンアウトと行の乗算
覚えておくべきこと:
- 結合は一致するペアごとに1行を生成するため、1対多の一致では「1」側が重複します。
- 多対多のキーでは行数が乗算されます。そのキーについて、3 x 4 = 12行になります。
- ファンアウトした結合をまたいで親の値を集計すると、合計や件数が過大になります。
- 子側を事前集計するか、正しい粒度で数える・合計することで解決します（例:
COUNT(DISTINCT)）。 - まず意図する粒度を明確にしてください。ファンアウトは、その粒度に反する場合にだけバグになります。
よくある質問
「JOINのファンアウトと行の増加」レッスンは無料ですか？
はい。「JOINのファンアウトと行の増加」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「JOINのファンアウトと行の増加」で何を学びますか？
JOINによってどちらのテーブルよりも多くの行が返る理由と、面接での確認方法を学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「JOINのファンアウトと行の増加」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- INNER JOINが行を照合する仕組み
- JOINにおけるONとWHEREの違い
- JOINのファンアウトと行の増加
- 3つ以上のテーブルをJOINする