【SQL】データ結合とJOIN句
目次
DBテーブルを結合しデータを取り出す
複数のテーブルからデータを結合して取り出すSQLのJOIN句の基本についてまとめます。
DB環境: MySQL 8、PostgreSQL 10
データ結合とは
データの結合は下の表のような互いに関連（リレーション）のある2つ以上のテーブル同士を横方向に繋げて行います。
book
| タイトル | 著者 | カテゴリID |
|---|---|---|
| 台湾の旅テク303 | 朝日新聞出版 | 3 |
| 絵で見てわかるITインフラの仕組み | 山崎 泰史 | 1 |
| シリコンバレー式超ライフハック | デイヴ・アスプリー | 2 |
| 試験によく出る 基本情報技術者試験問題集（午後） | 角谷 一成 | 1 |
| 週末バックパッカー | 我妻 弘崇 | 3 |
| ヒミツの赤ワイン | 吉田靖志 | - |
| なぜ?がわかるデータベース | 小笠原 種高 | 1 |
category
| カテゴリID | カテゴリ名 |
|---|---|
| 1 | コンピュータ・IT |
| 2 | ビジネス書 |
| 3 | 旅行 |
| 4 | コミック |
bookとcategoryを結合
| タイトル | 著者 | カテゴリID | カテゴリ名 |
|---|---|---|---|
| 台湾の旅テク303 | 朝日新聞出版 | 3 | 旅行 |
| 絵で見てわかるITインフラの仕組み | 山崎 泰史 | 1 | コンピュータ・IT |
| シリコンバレー式超ライフハック | デイヴ・アスプリー | 2 | ビジネス書 |
| 試験によく出る 基本情報技術者試験問題集（午後） | 角谷 一成 | 1 | コンピュータ・IT |
| 週末バックパッカー | 我妻 弘崇 | 3 | 旅行 |
| ヒミツの赤ワイン | 吉田靖志 | - | - |
| なぜ?がわかるデータベース | 小笠原 種高 | 1 | コンピュータ・IT |
結合するテーブル同士で互いに共通する列、bookの「カテゴリID」とテーブルの「ID」を結合の条件としています。
（条件の値をキーと呼ぶこともある）
結合の方法には内部結合と外部結合があり、SQLのJOIN句を使って実現します。
内部結合（INNER JOIN）
内部結合とはテーブル同士で結合条件に合致した行のみ選択結果に含める結合方法です。
キー（結合条件）となる列の値がどちらかのテーブルにしか存在しない行は選択結果として取り出すことが出来ません。
・タイトル、カテゴリ名を内部結合で取り出すSQL
SELECT title, category_name FROM book INNER JOIN category ON category_id = category.id
データ結合するbookとcategoryをINNER JOIN句で繋げ、ON句にはキーとなる列bookのcategory_idとcategoryのidを=（イコール）で関連付けます。
※列名がテーブル同士で重複する場合はテーブル名.列名のように指定します
結果
+------------------------------------------------------------------------+-------------------------+ | title | category_name | +------------------------------------------------------------------------+-------------------------+ | 絵で見てわかるITインフラの仕組み | コンピュータ・IT | | 試験によく出る 基本情報技術者試験問題集（午後） | コンピュータ・IT | | なぜ?がわかるデータベース | コンピュータ・IT | | シリコンバレー式超ライフハック | ビジネス書 | | 台湾の旅テク303 | 旅行 | | 週末バックパッカー | 旅行 | +------------------------------------------------------------------------+-------------------------+
カテゴリのID同士が合致しない行、タイトル「ヒミツの赤ワイン」とカテゴリ名「コミック」はそれぞれ一方にしかないため選択結果に含まれていません。
内部結合は下記のようにINNERを省略しJOINとするだけでも同じ結果を得ることが出来ます。
SELECT title, category_name FROM book JOIN category ON category_id = category.id
（JOINを使わない内部結合）
内部結合はJOIN句を使わず、以下のSQLでも実現可能です。
SELECT title, category_name FROM book,category WHERE category_id = category.id
FROM句の後には結合するテーブル名をJOIN句を使わずカンマで区切って記述し、結合条件はON句ではなくWHERE句を使って指定します。
（この方法は古く、現在は前者のJOINを使った方法が一般的なようです）
外部結合（OUTER JOIN）
外部結合は内部結合とは異なり、条件に指定した列の値が片側のテーブルにしか存在しない場合も選択結果に含める方法です。
外部結合には左（側）外部結合と右（側）外部結合があり、
結合に使用するOUTER JOIN句より前（左側）に指定するテーブルか、それよりも後（右側）に指定するテーブルかのどちらを主テーブルとするかによって使い分けます。
主テーブル側の行は結合条件を満たさない場合であってもすべて必ず出力します。
左外部結合（LEFT OUTER JOIN）
・タイトル、カテゴリ名を左外部結合で取り出すSQL
SELECT title, category_name FROM book LEFT OUTER JOIN category ON category_id = category.id
データ結合するbookとcategoryをLEFT OUTER JOIN句で繋げます。
結合条件は内部結合のときと同様ON句で指定します。
結果
+------------------------------------------------------------------------+-------------------------+ | title | category_name | +------------------------------------------------------------------------+-------------------------+ | 台湾の旅テク303 | 旅行 | | 絵で見てわかるITインフラの仕組み | コンピュータ・IT | | シリコンバレー式超ライフハック | ビジネス書 | | 試験によく出る 基本情報技術者試験問題集（午後） | コンピュータ・IT | | 週末バックパッカー | 旅行 | | ヒミツの赤ワイン | NULL | | なぜ?がわかるデータベース | コンピュータ・IT | +------------------------------------------------------------------------+-------------------------+
左外部結合なのでJOIN句よりも先に記述しているbookテーブルの全行が出力されています。
内部結合では選択結果に含まれなかった、タイトル「ヒミツの赤ワイン」の行が結果に含まれるようになりました。
カテゴリ名が存在しない列の値はNULLを表示します。
左外部結合は下記のようにOUTERを省略し、LEFT JOINと記述しても同じ結果となります。
SELECT title, category_name FROM book LEFT JOIN category ON category_id = category.id
右外部結合（RIGHT OUTER JOIN）
・タイトル、カテゴリ名を右外部結合で取り出すSQL
SELECT title, category_name FROM book RIGHT OUTER JOIN category ON category_id = category.id
データ結合するbookとcategoryをRIGHT OUTER JOIN句で繋げます。
※こちらもRIGHT JOINと省略して記述出来ます。
結果
+------------------------------------------------------------------------+-------------------------+ | title | category_name | +------------------------------------------------------------------------+-------------------------+ | NULL | コミック | | 絵で見てわかるITインフラの仕組み | コンピュータ・IT | | 試験によく出る 基本情報技術者試験問題集（午後） | コンピュータ・IT | | なぜ?がわかるデータベース | コンピュータ・IT | | シリコンバレー式超ライフハック | ビジネス書 | | 台湾の旅テク303 | 旅行 | | 週末バックパッカー | 旅行 | +------------------------------------------------------------------------+-------------------------+
右外部結合なので今度は、JOIN句よりも後に記述したcategoryテーブルの全行が出力されています。
内部結合では選択結果に含まれなかった、カテゴリ名「コミック」の行が結果に含まれるようになりました。
ちなみに、主テーブルを右にしているだけなので当然JOINの前後でテーブル順序を入れ替えるとbookが主テーブルとなるため、入れ替える前のLEFT OUTER JOINと同じ選択結果となります。
（ややこしいですが…）
2つは同じ結果となる
SELECT title, category_name FROM book LEFT OUTER JOIN category ON category_id = category.id
SELECT title, category_name FROM category RIGHT OUTER JOIN book ON category_id = category.id
完全外部結合 （FULL OUTER JOIN）
こちらは少し例外的なのですが、
前述の外部結合のように左右どちらかだけでなく、結合する左右両方のテーブルにある条件に合致しない行も含めて全行出力したい場合は完全外部結合（FULL OUTER JOIN）を使います。
結合する左右両テーブルにある条件に合致しない行も含め、全て選択する。
SELECT title, category_name FROM book FULL OUTER JOIN category ON category_id = category.id
（FULL OUTER JOINが使えない場合）
データベースソフトウェアのうち、PostgreSQLではFULL OUTER JOINを実行出来ますがMySQL（MariaDB）はFULL OUTER JOINをサポートしていないため代わりに下記の方法で完全外部結合を実現すること出来ます。
SELECT title, category_name FROM book LEFT JOIN category ON category_id = category.id
UNION
SELECT title, category_name FROM book RIGHT JOIN category ON category_id = category.id
左右の外部結合をUNION句で組み合わせることで実現します。
UNION句は横方向結合のJOIN句とは異なり、列が同じデータ同士を重複を省いて縦方向に組み合わせて取り出します。（集合という）
（まとめ）
JOINによるデータ結合方法をまとめると
結合するテーブルから
- 両方に共通する行だけを取り出したい場合（内部結合）
INNER JOIN （JOIN） - いずれか片側にしか存在しない行も含めて取り出したい場合（外部結合）
LEFT OUTER JOIN (LEFT JOIN), RIGHT OUTER JOIN (RIGHT JOIN)
外部結合でテーブルが２つだけでなく3つ以上となった場合、左側のテーブルを基準に繋げていくのが一般的であるため左外部結合のLEFT OUTER JOINを使用することが推奨されます。
Follow me!
