先日にも触れたが、「LEFT JOIN」って直積なのをついつい忘れがちになる。
メインデータを一回のSQL発行で取得して、
それをループして一件ずつサブデータをSQL発行で取得してマージしていく…、
みたいな処理を一回のSQL発行でできるのが「LEFT JOIN」なのかなと錯覚してしまう。
当然、名称取得などサブデータが一件の場合はその通りなのだが、
カウントした件数をマージしたりする場合は注意が必要だ。

これはOK。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       サブ表.サブ名称                                                      │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表                                                    │
│          ON サブ表.ID = メイン表.サブID;                                   │
└──────────────────────────────────────┘
サブ名称を参照するケースだ。

これはギリギリOK。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       COUNT(サブ表.ID) AS サブ該当件数                                     │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表                                                    │
│          ON サブ表.キーID = メイン表.サブキーID                            │
│    GROUP BY メイン表.ID;                                                   │
└──────────────────────────────────────┘
サブ表中にサブキーIDに該当するものが何件あるかをマージするケース。

でも、これはNG、件数が二つになったらNG。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       COUNT(サブ表1.ID) AS サブ1該当件数,                                  │
│       COUNT(サブ表2.ID) AS サブ2該当件数                                   │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表1                                                   │
│          ON サブ表1.キーID = メイン表.サブ1キーID                          │
│        LEFT JOIN サブ表2                                                   │
│          ON サブ表2.キーID = メイン表.サブ2キーID                          │
│    GROUP BY メイン表.ID;                                                   │
└──────────────────────────────────────┘
サブ1とサブ2で各々で件数を算出してくれるかと思いきや、直積により、
それぞれの該当件数が両者の掛け算の結果(つまり同一値)になってしまうのだ。

対抗策１、DISTINCTをつける。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       COUNT(DISTINCT サブ表1.ID) AS サブ1該当件数,                         │
│       COUNT(DISTINCT サブ表2.ID) AS サブ2該当件数                          │
│    FROM メイン表                                                           │
│        LEFT JOIN サブ表1                                                   │
│          ON サブ表1.キーID = メイン表.サブ1キーID                          │
│        LEFT JOIN サブ表2                                                   │
│          ON サブ表2.キーID = メイン表.サブ2キーID                          │
│    GROUP BY メイン表.ID;                                                   │
└──────────────────────────────────────┘
直積に対抗するにはDISTINCTとばかりに、各々にDISTINCTをつける。
こんなところにDISTINCTを置けるのか不安だったが意外にいける。

対抗策２、サブクエリとする。
┌──────────────────────────────────────┐
│SELECT メイン表.ID,                                                         │
│       (SELECT COUNT(サブ表1.ID)                                            │
│            FROM サブ表1                                                    │
│            WHERE サブ表1.キーID = メイン表.サブ1キーID                     │
│       ) AS サブ1該当件数,                                                  │
│       (SELECT COUNT(サブ表2.ID)                                            │
│            FROM サブ表2                                                    │
│            WHERE サブ表2.キーID = メイン表.サブ2キーID                     │
│       ) AS サブ2該当件数                                                   │
│    FROM メイン表;                                                          │
└──────────────────────────────────────┘
メインデータに対してサブデータをマージするというイメージには、
こっちの方が近いのだろう。
対抗策１だと直積で一旦メインデータを増殖させて、GROUP BYで収束させているが
これだとメインデータはそのままだ。
それにしても()の中に()の外側のメイン表が突然出てくる感じがして
若干違和感があった(JOINしてないから)、でもサブクエリってそんなもの。

対抗策３、UNIONを使う力業。
┌──────────────────────────────────────┐
│SELECT 新メイン表.ID,                                                       │
│       SUM(新メイン表.サブ1該当件数) AS サブ1該当件数,                      │
│       SUM(新メイン表.サブ2該当件数) AS サブ2該当件数                       │
│    FROM (SELECT メイン表.ID,                                               │
│                 NULL              AS サブ1該当件数,                        │
│                 NULL              AS サブ2該当件数                         │
│               FROM メイン表                                                │
│          UNION ALL                                                         │
│          SELECT メイン表.ID,                                               │
│                 COUNT(サブ表1.ID) AS サブ1該当件数,                        │
│                 NULL              AS サブ2該当件数                         │
│               FROM メイン表,                                               │
│                    サブ表1                                                 │
│               WHERE メイン表.サブ1キーID = サブ表1.キーID                  │
│               GROUP BY メイン表.ID                                         │
│          UNION ALL                                                         │
│          SELECT メイン表.ID,                                               │
│                 NULL              AS サブ1該当件数,                        │
│                 COUNT(サブ表2.ID) AS サブ2該当件数                         │
│               FROM メイン表,                                               │
│                    サブ表2                                                 │
│               WHERE メイン表.サブ2キーID = サブ表2.キーID                  │
│               GROUP BY メイン表.ID                                         │
│         ) AS 新メイン表                                                    │
│    GROUP BY 新メイン表.ID;                                                 │
└──────────────────────────────────────┘
こんな感じかな(動作確認してないからちょっと違うかも)。
対抗策３はあくまで余談です。