JOIN を実行すると同じ内容のレコードが複数含まれる現象について紹介します。
下記のテーブルを準備します。
| id | name | value | category | memo |
|---|---|---|---|---|
| 1 | Penguin | 300 | B | 南国にすむペンギンです |
| 2 | Whale | 420 | M | 北極海のクジラです |
| 3 | Moffu | 880 | NULL | よくわからない生き物です |
| 4 | Camel | 220 | M | 砂漠にすむラクダです |
| 5 | Owl | 90 | B | 関東のフクロウです |
| 6 | Duck | 120 | B | そこらへんのアヒルです |
| id | category | name | flag |
|---|---|---|---|
| 1 | B | 鳥類 | 1 |
| 2 | B | 鳥類 | 2 |
| 3 | M | 哺乳類 | 1 |
下記のSQLを実行します。
SELECT Working.name, Working.category, WorkingCategory.name FROM Working
LEFT JOIN WorkingCategory ON Working.category=WorkingCategory.category;
下記の結果となります。
| name | category | name |
|---|---|---|
| Penguin | B | 鳥類 |
| Penguin | B | 鳥類 |
| Whale | M | 哺乳類 |
| Moffu | NULL | NULL |
| Camel | M | 哺乳類 |
| Owl | B | 鳥類 |
| Owl | B | 鳥類 |
| Duck | B | 鳥類 |
| Duck | B | 鳥類 |
category の値がBのレコードは同じ内容のレコードが2つ含まれています。これは、WorkingCategory テーブルにcategoryの値が"B"で、flagの値が"1"と"2"のレコードの2種類があるため、それぞれが結合された結果2つの同じ値のレコードが出力されます。
INNER JOINを実行した場合もレコードは重複します。
SELECT Working.name, Working.category, WorkingCategory.name FROM Working
INNER JOIN WorkingCategory ON Working.category=WorkingCategory.category;
| name | category | name |
|---|---|---|
| Penguin | B | 鳥類 |
| Penguin | B | 鳥類 |
| Whale | M | 哺乳類 |
| Camel | M | 哺乳類 |
| Owl | B | 鳥類 |
| Owl | B | 鳥類 |
| Duck | B | 鳥類 |
| Duck | B | 鳥類 |
出力されるフィールドの値が全く同じ場合は DISTINCT を利用すると回避できます。
下記のSQL文を実行します。
SELECT DISTINCT Working.name, Working.category, WorkingCategory.name FROM Working
LEFT JOIN WorkingCategory ON Working.category=WorkingCategory.category;
結果は以下になります。
| name | category | name |
|---|---|---|
| Camel | M | 哺乳類 |
| Duck | B | 鳥類 |
| Moffu | NULL | NULL |
| Owl | B | 鳥類 |
| Penguin | B | 鳥類 |
| Whale | M | 哺乳類 |
SELECT DISTINCT Working.name, Working.category, WorkingCategory.name FROM Working
INNER JOIN WorkingCategory ON Working.category=WorkingCategory.category;
結果は以下になります。
| name | category | name |
|---|---|---|
| Camel | M | 哺乳類 |
| Duck | B | 鳥類 |
| Owl | B | 鳥類 |
| Penguin | B | 鳥類 |
| Whale | M | 哺乳類 |
DISTINCT 文の詳細についてはこちらの記事を参照してください。
条件で絞り込むことで、結合するレコードが1つになる場合は、結合条件を追加することで回避できます。on節の条件式に and を記述することで複数の条件を結合条件に設定できます。
今回の例の場合WorkingCategory テーブルのflagが1の場合に限り結合すれば、結合するレコードを1つにできます。下記のSQL文実行します。
SELECT Working.name, Working.category, WorkingCategory.name FROM Working
LEFT JOIN WorkingCategory ON Working.category=WorkingCategory.category AND WorkingCategory.flag=1;
結果は以下になります。
| name | category | name |
|---|---|---|
| Penguin | B | 鳥類 |
| Whale | M | 哺乳類 |
| Moffu | NULL | NULL |
| Camel | M | 哺乳類 |
| Owl | B | 鳥類 |
| Duck | B | 鳥類 |
新宿の路地裏にある小さな定食屋。木のカウンターに昼の光が斜めに差し込み、味噌汁の湯気が白く揺れている。
プログラマーの彩と DB エンジニアの健司は、揚げたてのアジフライ定食を前にノート PC を覗き込んでいた。
「健司さん、このクエリなんだけど」
彩は箸を止め、画面の一行を指差す。
SELECT * FROM orders o LEFT JOIN order_items i ON o.id = i.order_id
「同じ注文が何度も出てきて、売上が倍々ゲームになっちゃうの。原因がわからなくて」
健司は湯気越しに目を細める。
「orders と order_items は一対多だろ。JOIN した時点で明細の数だけ行が増える。それをそのまま集計したら、そりゃあアジフライが三枚四枚になるさ」
彩は衣のサクッという音を立てながら首をかしげる。
「頭では分かってるつもりだったんだけど、実際に数が合わないと焦るよね」
健司は卓上のしょうゆ差しを持ち上げた。
「定食一皿にしょうゆを三回かければ、見かけ上は定食が三つ並んだように見える。でも定食は一皿だ。数えるときは主キーで数えるか、明細を先にまとめればいい」
彩は眉をひそめ、箸の先で皿をつついた。
「待って、しょうゆを何回振ったかで定食が増えるって、いまいちピンとこないんだけど。ソースを二回かけたらカツが二枚になるわけじゃないでしょ？」
健司は「ああ、たしかに」と笑い、メモ帳にさらさらと書き込む。
SELECT o.id, SUM(i.quantity) AS total_qty
FROM orders o
LEFT JOIN (SELECT order_id, SUM(quantity) AS quantity FROM order_items GROUP BY order_id) i
ON o.id = i.order_id
GROUP BY o.id
彩の顔がぱっと明るくなる。
「これなら定食は一行、しょうゆは合計一列！ ダイエット中でも安心だね」
ちょうどそのとき、店員が皿を運んできた。アジフライが三枚も乗っている。
「え、私一枚しか頼んでないんだけど」
店員は伝票を覗き込み、困ったように笑った。
「ご注文は一皿ですが、明細が三行ありまして」
健司が肩をすくめる。
「ほら、LEFT JOIN の罠そのままだな」
彩は苦笑しつつも、衣のカリカリをつまむ。
「DISTINCT で一枚にして返してもらおうかな」
店員は慌てて厨房へ戻り、皿を引っ込めた。だが数分後、戻ってきたのは半切れサイズのアジフライが一枚だけ。
「INNER JOIN に変更していただきました！」
彩は思わず叫ぶ。
「全部なくなるんかい！」
店内がどっと笑いに包まれた。健司は照れたように頭をかき、彩はその小さくなったフライをかじる。
重複レコードもフライも、数え方次第で腹の満ち具合が変わる。ランチタイムの定食屋に、SQL とアジフライの香ばしい余韻が漂っていた。
