ども、シローです。
前回(https://shiro-secret-base.com/?p=953)はテーブル結合(JOIN)における内部結合と外部結合の違いについて整理しましたが
結合先のテーブルを結合元のデータの抽出条件に使う場合ですと、JOINを使わなくてもEXISTS
でなんとかなったりします。
出力したいデータの要件に合わせて、JOIN
かEXISTS
かを使い分けていきたいものです。
とりあえず結論
- 別テーブルの情報を出力対象にしたい場合は
JOIN
- 別テーブルの情報を抽出条件に使用するだけの場合は
EXISTS
SQLマンなら多分使い分けていることでしょう、
「お、おう・・」と思った人は次に進んでください。
サンプルデータ
pokemon
code | varchar(20) |
name | varchar(20) |
pokemon.csv(pokemon)
pokemon_type
code | varchar(20) |
type | varchar(20) |
display_order | int(11) |
pokemon_type.csv(pokemon_type)
pokemon_abillity
code | varchar(20) |
abillity | varchar(20) |
is_hidden | tinyint(1) |
pokemon_abillity.csv(pokemon_abillity)
テーブルにファイルを読み込む方法はこちらの記事で紹介(https://shiro-secret-base.com/?p=949)
JOINを使うケース
JOIN
を使う場合は、SELECT
で出力するカラムに別テーブルの情報を付け加えたい時です。
例えば、pokemonテーブルからポケモンの名前、pokemon_typeテーブルからタイプ1、タイプ2をpokemon.name, type1.type, type2.type
(type1,type2は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 |
MySQL root@(none):db_test> SELECT pokemon.name, 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; +------------+------------+------------+ | name | type1 | type2 | +------------+------------+------------+ | リザードン | ほのお | ひこう | | カイリュー | ドラゴン | ひこう | | ルカリオ | かくとう | はがね | | ラプラス | みず | こおり | | ゲンガー | ゴースト | どく | | レックウザ | ドラゴン | ひこう | | ハッサム | むし | はがね | | ゲッコウガ | みず | あく | | ミミッキュ | ゴースト | フェアリー | | ジラーチ | はがね | エスパー | | ピカチュウ | でんき | <null> | | イーブイ | ノーマル | <null> | | ミュウ | エスパー | <null> | | カビゴン | ノーマル | <null> | | ブースター | ほのお | <null> | | ゼニガメ | みず | <null> | | ミュウツー | エスパー | <null> | | サーナイト | エスパー | <null> | | ピチュー | でんき | <null> | | プリン | ノーマル | <null> | | アチャモ | ほのお | <null> | | コダック | みず | <null> | | グレイシア | こおり | <null> | | ニャース | ノーマル | <null> | | ブラッキー | あく | <null> | | ハクリュー | ドラゴン | <null> | | ニンフィア | フェアリー | <null> | +------------+------------+------------+ 27 rows in set Time: 0.008s |
となります。
EXISTSを使うケース
EXISTS
はWHERE EXISTS (サブクエリ)
というふうにサブクエリ内で検索対象のテーブルのレコードが存在するかどうかという条件式として使用します。
あくまで検索条件として他のテーブルを参照しているので出力するカラムには含まれないわけですね。
例えば、特性で「シンクロ」を持つポケモンをpokemon.code, pokemon.name
という風に出力したい場合は
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
MySQL root@(none):db_test> SELECT pokemon.code, pokemon.name -> FROM pokemon -> WHERE EXISTS ( -> SELECT 1 FROM pokemon_abillity WHERE pokemon.code = pokemon_abillity.code -> AND pokemon_abillity.abillity = 'シンクロ' -> ); +--------+------------+ | code | name | +--------+------------+ | ID_151 | ミュウ | | ID_197 | ブラッキー | | ID_282 | サーナイト | +--------+------------+ 3 rows in set Time: 0.007s |
という風になります。
もちろん、JOIN
と組み合わせてタイプを合わせて出力することもできます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
MySQL root@(none):db_test> SELECT pokemon.code, pokemon.name, 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 -> WHERE EXISTS ( -> SELECT 1 FROM pokemon_abillity WHERE pokemon.code = pokemon_abillity.code -> AND pokemon_abillity.abillity = 'シンクロ' -> ); +--------+------------+----------+--------+ | code | name | type1 | type2 | +--------+------------+----------+--------+ | ID_151 | ミュウ | エスパー | <null> | | ID_282 | サーナイト | エスパー | <null> | | ID_197 | ブラッキー | あく | <null> | +--------+------------+----------+--------+ 3 rows in set Time: 0.008s |
サブクエリでSELECT 1 FROM
としているわけ
EXISTS
はあくまでレコードが存在するかの条件なのでサブクエリで出力するカラムはなんでも良いのですね
どうせなら出力する内容が最小限の方が処理速度が早くなるだろうということでSELECT 1 FROM pokemon_abillity
としています。
JOINでもEXISTSと同じことができるのだけど・・
JOIN
で連結してカラムにそのテーブルの内容を出力しなければいいじゃん、と思ったあなた。
まあ、確かにそれはある意味そうなんだけど・・
それは結合元と結合先のテーブルが1:1の場合に限るんですよね
もし、1 : N の関係ですと結合先のレコードが重複して出力されてしまうことがあります。
そして重複するレコードはDISTINCT
を使えば一つにすることができるのですけど、
これが思った以上に処理速度がかかるため、集計データなどの大量のデータを出力したい場合ですと、無視できない時間になってしまいます。
例:pokemonに新しくフリーザー、サンダー、ファイヤーを追加し、pokemon_abillityには対応するレコードがない場合
新たにpokemonテーブルにフリーザー、サンダー、ファイヤーを追加します。
1 2 3 4 5 6 |
MySQL root@(none):db_test> INSERT INTO pokemon (code, name) VALUES -> ('ID_144','フリーザー'), -> ('ID_145','サンダー'), -> ('ID_146','ファイヤー'); Query OK, 3 rows affected Time: 0.004s |
そして、pokemon_abillityにレコードが登録されているポケモンを出力したいとします。
JOINの場合は重複してしまう・・
ただ特性が設定されているかを知りたいのに同じレコードがいっぱい出てしまいます。(一つのポケモンに紐づく特性の数だけ)
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 |
MySQL root@(none):db_test> SELECT pokemon.code, pokemon.name -> FROM pokemon -> INNER JOIN pokemon_abillity -> ON pokemon.code = pokemon_abillity.code; +--------+------------+ | code | name | +--------+------------+ | ID_025 | ピカチュウ | | ID_025 | ピカチュウ | | ID_133 | イーブイ | | ID_133 | イーブイ | | ID_133 | イーブイ | | ID_006 | リザードン | | ID_006 | リザードン | | ID_149 | カイリュー | | ID_149 | カイリュー | | ID_448 | ルカリオ | | ID_448 | ルカリオ | | ID_448 | ルカリオ | ..... | ID_052 | ニャース | | ID_052 | ニャース | | ID_052 | ニャース | | ID_197 | ブラッキー | | ID_197 | ブラッキー | | ID_148 | ハクリュー | | ID_148 | ハクリュー | | ID_700 | ニンフィア | | ID_700 | ニンフィア | | ID_658 | ゲッコウガ | | ID_658 | ゲッコウガ | | ID_778 | ミミッキュ | | ID_385 | ジラーチ | +--------+------------+ 58 rows in set Time: 0.012s |
DISTINCTで絞り込む
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 |
MySQL root@(none):db_test> SELECT DISTINCT pokemon.code, pokemon.name -> FROM pokemon -> INNER JOIN pokemon_abillity -> ON pokemon.code = pokemon_abillity.code; +--------+------------+ | code | name | +--------+------------+ | ID_025 | ピカチュウ | | ID_133 | イーブイ | | ID_006 | リザードン | | ID_149 | カイリュー | | ID_448 | ルカリオ | | ID_131 | ラプラス | | ID_151 | ミュウ | | ID_143 | カビゴン | | ID_094 | ゲンガー | | ID_136 | ブースター | | ID_007 | ゼニガメ | | ID_150 | ミュウツー | | ID_282 | サーナイト | | ID_384 | レックウザ | | ID_172 | ピチュー | | ID_039 | プリン | | ID_255 | アチャモ | | ID_054 | コダック | | ID_471 | グレイシア | | ID_212 | ハッサム | | ID_052 | ニャース | | ID_197 | ブラッキー | | ID_148 | ハクリュー | | ID_700 | ニンフィア | | ID_658 | ゲッコウガ | | ID_778 | ミミッキュ | | ID_385 | ジラーチ | +--------+------------+ 27 rows in set Time: 0.005s |
GROUP BYで絞り込む
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 |
MySQL root@(none):db_test> SELECT pokemon.code, pokemon.name -> FROM pokemon -> INNER JOIN pokemon_abillity -> ON pokemon.code = pokemon_abillity.code -> GROUP BY pokemon.code, pokemon.name; +--------+------------+ | 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.009s |
EXISTSを使うケース
EXISTS
では重複を防いで出力してくれます。
JOIN
で結合した後にDISTINCT
やGROUP BY
で一つに絞る必要はありません。
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 |
MySQL root@(none):db_test> SELECT pokemon.code, pokemon.name -> FROM pokemon -> WHERE EXISTS ( -> SELECT 1 FROM pokemon_abillity WHERE pokemon.code = pokemon_abillity.code -> ); +--------+------------+ | 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 |
個人的には、他のテーブルに存在するかどうかを確かめたいだけならEXISTS
を使うようにするのがオススメです。
まとめ
- 他のテーブルの内容を出力する場合のみに
JOIN
を使おう - 他のテーブルを検索条件に使用したい場合は
EXISTS
を使おう
2012年の改訂版から5年半ぶりの改訂データベースに触れるのが本当に初めてという方に向けて、「データベースとは何か」という基本中の基本から、MySQLを使ったデータベースの作成と操作、PHPとの連携によるWebアプリケーション作成の入り口までを、豊富な図とサンプルにより解説します。5年間で周辺環境が変わりましたので、そちらを一新しての刊行です。