ども、シローです。
今回はSQLの内部結合と外部結合の違いについてです。
両者では最終的に抽出されるデータが異なるので要件に合わせて使い分けていきたいものです。
とりあえず結論
- 内部結合(JOIN, INNER JOIN)は結合先に結合条件に合うデータがない場合は結合元を出力対象にしない
- 外部結合は
- LEFT JOIN: 結合先に結合条件に合うデータがなくても結合元だけは出力対象にする
- RIGHT JOIN: 結合先に結合条件に合うデータがなくても結合先だけは出力対象にする(LEFT JOINの逆)
わかっている人はこのニュアンスで通じてくれるはずなので、もしパッと来なかったら次進んでくださいー
サンプルデータ
解説するにあたって、こちらのデータを用いていきます。
ポケモンってSQLの勉強に便利ですね
pokemonテーブル
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
MySQL root@(none):db_test> SELECT * FROM pokemon; +--------+------------+ | code | name | +--------+------------+ | ID_006 | リザードン | | ID_007 | ゼニガメ | | ID_025 | ピカチュウ | | ID_039 | プリン | | ID_052 | ニャース | | ID_054 | コダック | | ID_094 | ゲンガー | | ID_131 | ラプラス | | ID_133 | イーブイ | | ID_136 | ブースター | | ID_143 | カビゴン | | ID_148 | ハクリュー | | ID_149 | カイリュー | | ID_150 | ミュウツー | | ID_151 | ミュウ | | ID_172 | ピチュー | | ID_197 | ブラッキー | | ID_212 | ハッサム | | ID_255 | アチャモ | | ID_282 | サーナイト | | ID_384 | レックウザ | | ID_385 | ジラーチ | | ID_448 | ルカリオ | | ID_471 | グレイシア | | ID_658 | ゲッコウガ | | ID_700 | ニンフィア | | ID_778 | ミミッキュ | +--------+------------+ 27 rows in set Time: 0.006s |
pokemon_typeテーブル
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
MySQL root@(none):db_test> SELECT * FROM pokemon_type; +--------+------------+---------------+ | code | type | display_order | +--------+------------+---------------+ | ID_025 | でんき | 1 | | ID_133 | ノーマル | 1 | | ID_006 | ほのお | 1 | | ID_006 | ひこう | 2 | | ID_149 | ドラゴン | 1 | | ID_149 | ひこう | 2 | | ID_448 | かくとう | 1 | | ID_448 | はがね | 2 | | ID_131 | みず | 1 | | ID_131 | こおり | 2 | | ID_151 | エスパー | 1 | | ID_143 | ノーマル | 1 | | ID_094 | ゴースト | 1 | | ID_094 | どく | 2 | | ID_136 | ほのお | 1 | | ID_007 | みず | 1 | | ID_150 | エスパー | 1 | | ID_282 | エスパー | 1 | | ID_384 | ドラゴン | 1 | | ID_384 | ひこう | 2 | | ID_172 | でんき | 1 | | ID_039 | ノーマル | 1 | | ID_255 | ほのお | 1 | | ID_054 | みず | 1 | | ID_471 | こおり | 1 | | ID_212 | むし | 1 | | ID_212 | はがね | 2 | | ID_052 | ノーマル | 1 | | ID_197 | あく | 1 | | ID_148 | ドラゴン | 1 | | ID_700 | フェアリー | 1 | | ID_658 | みず | 1 | | ID_658 | あく | 2 | | ID_778 | ゴースト | 1 | | ID_778 | フェアリー | 2 | | ID_385 | はがね | 1 | | ID_385 | エスパー | 2 | +--------+------------+---------------+ 37 rows in set Time: 0.007s |
結合元と結合先
結合には結合元と結合先があります。
例えば、「pokemonテーブルにpokemon_typeを結合したい」という場合ですと
pokemonが結合元、pokemon_typeが結合先になって、
|
1 2 3 |
MySQL root@(none):db_test> SELECT * FROM pokemon -> JOIN pokemon_type -> ON pokemon.code = pokemon_type.code; |
逆に、「pokemon_typeにpokemonを結合したい」という場合ですと
pokemonが結合先、pokemon_typeが結合元になります。
|
1 2 3 |
MySQL root@(none):db_test> SELECT * FROM pokemon_type -> JOIN pokemon -> ON pokemon_type.code = pokemon.code; |
SQLの文法で言えば
- FROM の後に指定するテーブルが結合元
- JOINの後に指定するテーブルが結合先
になりますねー
では、これから内部結合と外部結合の違いについて解説していきます。
内部結合
内部結合は結合先に結合条件に合うレコードが存在しない場合には結合元のレコードを出力しません。
文法ですと、JOIN 結合先 ON 結合条件 またはINNER JOIN 結合先 ON 結合条件となります。
例えばエスパータイプのポケモンのみをpokemonテーブルから抽出したい、という場合ですと
次のように
|
1 2 3 4 5 6 7 8 9 10 11 |
MySQL root@(none):db_test> SELECT * FROM pokemon -> JOIN pokemon_type -> ON pokemon.code = pokemon_type.code AND pokemon_type.type = 'エスパー'; +--------+------------+--------+----------+---------------+ | code | name | code | type | display_order | +--------+------------+--------+----------+---------------+ | ID_151 | ミュウ | ID_151 | エスパー | 1 | | ID_150 | ミュウツー | ID_150 | エスパー | 1 | | ID_282 | サーナイト | ID_282 | エスパー | 1 | | ID_385 | ジラーチ | ID_385 | エスパー | 2 | +--------+------------+--------+----------+---------------+ |
なります。
お気づきの通り、エスパータイプではないポケモンはpokemon_typeの結合条件から外れるので抽出されてませんね。
このように結合先に結合条件に会ったレコードが存在する場合に結合元を抽出するのが内部結合です。
外部結合
外部結合にはLEFT JOINとRIGHT JOINがありますが、よく使われるのはLEFT JOINの方なのでこちらを基準として解説します。
LEFT JOINとRIGHT JOINは互いに逆の関係になっているという感じで大丈夫だと思ってます。（と言いつつ、別のブログ->https://sql-oracle.com/sqlserver/?p=1130
外部結合は結合条件に合致してなくても結合元のレコードを出力します。
例えば、第２タイプがないポケモンでもid, name, type1, type2(ない場合はNULL)という形式で出力したい場合は
次のように
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
MySQL root@(none):db_test> SELECT pokemon.*, type1.type AS type1, type2.type AS type2 FROM pokemon -> LEFT JOIN pokemon_type AS type1 -> ON pokemon.code = type1.code AND type1.display_order = 1 -> LEFT JOIN pokemon_type AS type2 -> ON pokemon.code = type2.code AND type2.display_order = 2; <= タイプ2がない場合でもpokemonのレコードを出力する！ +--------+------------+------------+------------+ | code | name | type1 | type2 | +--------+------------+------------+------------+ | ID_006 | リザードン | ほのお | ひこう | | ID_149 | カイリュー | ドラゴン | ひこう | | ID_448 | ルカリオ | かくとう | はがね | | ID_131 | ラプラス | みず | こおり | | ID_094 | ゲンガー | ゴースト | どく | | ID_384 | レックウザ | ドラゴン | ひこう | | ID_212 | ハッサム | むし | はがね | | ID_658 | ゲッコウガ | みず | あく | | ID_778 | ミミッキュ | ゴースト | フェアリー | | ID_385 | ジラーチ | はがね | エスパー | | ID_025 | ピカチュウ | でんき | <null> | | ID_133 | イーブイ | ノーマル | <null> | | ID_151 | ミュウ | エスパー | <null> | | ID_143 | カビゴン | ノーマル | <null> | | ID_136 | ブースター | ほのお | <null> | | ID_007 | ゼニガメ | みず | <null> | | ID_150 | ミュウツー | エスパー | <null> | | ID_282 | サーナイト | エスパー | <null> | | ID_172 | ピチュー | でんき | <null> | | ID_039 | プリン | ノーマル | <null> | | ID_255 | アチャモ | ほのお | <null> | | ID_054 | コダック | みず | <null> | | ID_471 | グレイシア | こおり | <null> | | ID_052 | ニャース | ノーマル | <null> | | ID_197 | ブラッキー | あく | <null> | | ID_148 | ハクリュー | ドラゴン | <null> | | ID_700 | ニンフィア | フェアリー | <null> | +--------+------------+------------+------------+ 27 rows in set Time: 0.007s |
なります。
仮に内部結合だった場合
もし、上記のSQL文のLEFT JOINがJOINだった場合、すなわち内部結合だと
第二タイプがないポケモンは出力されません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
MySQL root@(none):db_test> SELECT pokemon.*, type1.type AS type1, type2.type AS type2 FROM pokemon -> JOIN pokemon_type AS type1 -> ON pokemon.code = type1.code AND type1.display_order = 1 -> JOIN pokemon_type AS type2 -> ON pokemon.code = type2.code AND type2.display_order = 2; +--------+------------+----------+------------+ | code | name | type1 | type2 | +--------+------------+----------+------------+ | ID_006 | リザードン | ほのお | ひこう | | ID_149 | カイリュー | ドラゴン | ひこう | | ID_448 | ルカリオ | かくとう | はがね | | ID_131 | ラプラス | みず | こおり | | ID_094 | ゲンガー | ゴースト | どく | | ID_384 | レックウザ | ドラゴン | ひこう | | ID_212 | ハッサム | むし | はがね | | ID_658 | ゲッコウガ | みず | あく | | ID_778 | ミミッキュ | ゴースト | フェアリー | | ID_385 | ジラーチ | はがね | エスパー | +--------+------------+----------+------------+ 10 rows in set Time: 0.006s |
このように
- 第二タイプのないポケモン含めて、全ポケモンのタイプを知りたい→外部結合
- 第二タイプまであるポケモンのみ知りたい→内部結合
というふうに内部結合か外部結合かを選ぶことができますね
まとめ
- 内部結合：結合先に条件にマッチしたレコードがあれば結合先も出力
- 外部結合(LEFT JOIN)：結合先に条件にマッチしたレコードがなければ結合先は出力しない
要件に合わせて使い分けていきましょー
基礎からのMySQL 第3版 (基礎からシリーズ)
2012年の改訂版から5年半ぶりの改訂データベースに触れるのが本当に初めてという方に向けて、「データベースとは何か」という基本中の基本から、MySQLを使ったデータベースの作成と操作、PHPとの連携によるWebアプリケーション作成の入り口までを、豊富な図とサンプルにより解説します。5年間で周辺環境が変わりましたので、そちらを一新しての刊行です。
2012年の改訂版から5年半ぶりの改訂データベースに触れるのが本当に初めてという方に向けて、「データベースとは何か」という基本中の基本から、MySQLを使ったデータベースの作成と操作、PHPとの連携によるWebアプリケーション作成の入り口までを、豊富な図とサンプルにより解説します。5年間で周辺環境が変わりましたので、そちらを一新しての刊行です。