今回はSQL文のテーブル結合について、例を出して簡単にまとめます。
プログラムを作る時に必要になるだけでなく、テストデータを確認する時に使用することもありますし、障害対応で時間が限られている時にアドリブで入力することもあります。
慣れておいて損はない文法なので、開発作業で使う機会があれば積極的に使うことを勧めます。
以下では、結合の種類について簡単に書いた後、結合例を記載します。
【結合の種類】
結合には下記の五種類があります。
・内部結合
JOIN句の左側のテーブルと右側のテーブルについて、結合条件に合致するレコードのみ出力する。
・左外部結合
JOIN句の左側のテーブルについては、結合条件に合致するレコードのみでなく、結合条件に合致しないレコードも出力する。
結合条件に合致しないレコードについては、右側のテーブルの項目は全項目NULLとする。
・右外部結合
JOIN句の左側のテーブルについては、結合条件に合致するレコードのみでなく、結合条件に合致しないレコードも出力する。
結合条件に合致しないレコードについては、左側のテーブルの項目は全項目NULLとする。
・完全外部結合
JOIN句の左側のテーブルと右側のテーブルについて、結合条件に合致するレコードのみでなく、結合条件に合致しないレコードも出力する。
結合条件に合致しないレコードについては、片方のテーブルの項目は全項目NULLとする。
・直積結合
JOIN句の左側のテーブルと右側のテーブルについて、全てのレコードの組み合わせを出力する。
【対象テーブル例】
・子会社A取扱商品
+--------+----------+
| 商品ID | 商品名 |
+--------+----------+
| 1 | 商品Ａ１ |
| 2 | 商品Ａ２ |
| 3 | 商品Ａ３ |
+--------+----------+・子会社B取扱商品
+--------+----------+
| 商品ID | 商品名 |
+--------+----------+
| 1 | 商品Ｂ１ |
| 2 | 商品Ｂ２ |
| 4 | 商品Ｂ４ |
+--------+----------+※両テーブル共に商品IDは主キー
【結合例】
※「AS」「INNER」「OUTER」は省略可能
※「AS」でテーブル別名を使用しない場合は、正式なテーブル名をカラム名の前につけて参照する
※ON句の代わりにWHERE句で結合条件を指定することも可能
・内部結合
SELECT A.商品ID,
A.商品名 AS A社商品名,
B.商品名 AS B社商品名
FROM 子会社A取扱商品 AS A
INNER JOIN 子会社B取扱商品 AS B
ON A.商品ID = B.商品ID
ORDER BY A.商品ID;↓
+--------+-----------+-----------+
| 商品ID | A社商品名 | B社商品名 |
+--------+-----------+-----------+
| 1 | 商品Ａ１ | 商品Ｂ１ |
| 2 | 商品Ａ２ | 商品Ｂ２ |
+--------+-----------+-----------+・左外部結合
SELECT A.商品ID,
A.商品名 AS A社商品名,
B.商品名 AS B社商品名
FROM 子会社A取扱商品 AS A
LEFT OUTER JOIN 子会社B取扱商品 AS B
ON A.商品ID = B.商品ID
ORDER BY A.商品ID;↓
+--------+-----------+-----------+
| 商品ID | A社商品名 | B社商品名 |
+--------+-----------+-----------+
| 1 | 商品Ａ１ | 商品Ｂ１ |
| 2 | 商品Ａ２ | 商品Ｂ２ |
| 3 | 商品Ａ３ | NULL |
+--------+-----------+-----------+・右外部結合
SELECT B.商品ID,
A.商品名 AS A社商品名,
B.商品名 AS B社商品名
FROM 子会社A取扱商品 AS A
RIGHT OUTER JOIN 子会社B取扱商品 AS B
ON A.商品ID = B.商品ID
ORDER BY B.商品ID;↓
+--------+-----------+-----------+
| 商品ID | A社商品名 | B社商品名 |
+--------+-----------+-----------+
| 1 | 商品Ａ１ | 商品Ｂ１ |
| 2 | 商品Ａ２ | 商品Ｂ２ |
| 4 | NULL | 商品Ｂ４ |
+--------+-----------+-----------+・完全外部結合
SELECT A.商品ID AS A社商品ID,
A.商品名 AS A社商品名,
B.商品ID AS B社商品ID,
B.商品名 AS B社商品名
FROM 子会社A取扱商品 AS A
FULL OUTER JOIN 子会社B取扱商品 AS B
ON A.商品ID = B.商品ID
ORDER BY A.商品ID,B.商品ID;↓
+-----------+-----------+-----------+-----------+
| A社商品ID | A社商品名 | B社商品ID | B社商品名 |
+-----------+-----------+-----------+-----------+
| 1 | 商品Ａ１ | 1 | 商品Ｂ１ |
| 2 | 商品Ａ２ | 2 | 商品Ｂ２ |
| 3 | 商品Ａ３ | NULL | NULL |
| NULL | NULL | 4 | 商品Ｂ４ |
+-----------+-----------+-----------+-----------+・直積結合
SELECT A.商品ID AS A社商品ID,
A.商品名 AS A社商品名,
B.商品ID AS B社商品ID,
B.商品名 AS B社商品名
FROM 子会社A取扱商品 AS A
CROSS JOIN 子会社B取扱商品 AS B
ORDER BY A.商品ID,B.商品ID;↓
+-----------+-----------+-----------+-----------+
| A社商品ID | A社商品名 | B社商品ID | B社商品名 |
+-----------+-----------+-----------+-----------+
| 1 | 商品Ａ１ | 1 | 商品Ｂ１ |
| 1 | 商品Ａ１ | 2 | 商品Ｂ２ |
| 1 | 商品Ａ１ | 4 | 商品Ｂ４ |
| 2 | 商品Ａ２ | 1 | 商品Ｂ１ |
| 2 | 商品Ａ２ | 2 | 商品Ｂ２ |
| 2 | 商品Ａ２ | 4 | 商品Ｂ４ |
| 3 | 商品Ａ３ | 1 | 商品Ｂ１ |
| 3 | 商品Ａ３ | 2 | 商品Ｂ２ |
| 3 | 商品Ａ３ | 4 | 商品Ｂ４ |
+-----------+-----------+-----------+-----------+以下、実務上の補足です。
・頻出の結合
五種類の結合の中では、内部結合と左外部結合が頻出です。
・外部結合による存在チェック
外部結合は存在チェックに使うこともあります。
結合条件に主キーを指定した場合、結合条件に合致しないケース以外で主キー項目がNULLになることはないので、主キー項目がNULLなら存在しない、NULLでなければ存在する、と判定することができます。
存在チェックは副問い合わせ(IN句)やEXISTS句でも可能ですが、外部結合を使うと他テーブルの情報を取得しつつ存在チェックも同時に行えるので、取得結果を一つにまとめたい時やループで１レコードずつ回していく時等に効果を発揮します。
もちろん、これだけを覚えれば良いというわけでなく、プログラムの可読性を考えると本当に存在チェックだけをしたい時は副問い合わせ(IN句)やEXISTS句を使うべきですし、性能要件がシビアな時もどれを採用するのがベストか調査・検討する必要があります。
・バグによる直積結合
直積結合は、意図して使うことよりも、バグによりそうなってしまうことの方が多いです。
具体的には「テーブル結合の失敗パターン」に書いていますが、結合キーの指定が不足することで直積結合になってしまいます。
直積結合がどのような結合なのかを知っておくことで、この類のバグをすぐに見つけることができるので、意図的に使わないにしても知っておいて損はありません。
いかがでしたでしょうか。
今回の記事では、SQLの基礎的な文法であり、初学者が躓きやすい文法であるテーブル結合について取り扱いました。
基本情報処理技術者試験のような試験の対策を意識しつつ、情報を補足し実務でも役立てやすくなるように意識しました。
SQLの文法については、これからも記事を書いていこうと思います！
コメント