SQL

ポケモンで解説するSQLのEXISTSとJOINの使い分け

ども、シローです。
前回(https://shiro-secret-base.com/?p=953)はテーブル結合(JOIN)における内部結合と外部結合の違いについて整理しましたが

結合先のテーブルを結合元のデータの抽出条件に使う場合ですと、JOINを使わなくてもEXISTSでなんとかなったりします。

出力したいデータの要件に合わせて、JOINEXISTSかを使い分けていきたいものです。

とりあえず結論

  • 別テーブルの情報を出力対象にしたい場合は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のエイリアス)と言うふうに出力したい場合は

となります。

EXISTSを使うケース

EXISTSWHERE EXISTS (サブクエリ)というふうにサブクエリ内で検索対象のテーブルのレコードが存在するかどうかという条件式として使用します。

あくまで検索条件として他のテーブルを参照しているので出力するカラムには含まれないわけですね。

例えば、特性で「シンクロ」を持つポケモンをpokemon.code, pokemon.nameという風に出力したい場合は

という風になります。

もちろん、JOINと組み合わせてタイプを合わせて出力することもできます。

サブクエリでSELECT 1 FROMとしているわけ

EXISTSはあくまでレコードが存在するかの条件なのでサブクエリで出力するカラムはなんでも良いのですね

どうせなら出力する内容が最小限の方が処理速度が早くなるだろうということでSELECT 1 FROM pokemon_abillityとしています。

 

JOINでもEXISTSと同じことができるのだけど・・

JOINで連結してカラムにそのテーブルの内容を出力しなければいいじゃん、と思ったあなた。

まあ、確かにそれはある意味そうなんだけど・・

それは結合元と結合先のテーブルが1:1の場合に限るんですよね

もし、1 : N の関係ですと結合先のレコードが重複して出力されてしまうことがあります。

そして重複するレコードはDISTINCTを使えば一つにすることができるのですけど、

これが思った以上に処理速度がかかるため、集計データなどの大量のデータを出力したい場合ですと、無視できない時間になってしまいます

例:pokemonに新しくフリーザー、サンダー、ファイヤーを追加し、pokemon_abillityには対応するレコードがない場合

新たにpokemonテーブルにフリーザー、サンダー、ファイヤーを追加します。

そして、pokemon_abillityにレコードが登録されているポケモンを出力したいとします。

JOINの場合は重複してしまう・・

ただ特性が設定されているかを知りたいのに同じレコードがいっぱい出てしまいます。(一つのポケモンに紐づく特性の数だけ)

DISTINCTで絞り込む

 

GROUP BYで絞り込む

 

EXISTSを使うケース

EXISTSでは重複を防いで出力してくれます。

JOINで結合した後にDISTINCTGROUP BYで一つに絞る必要はありません。

個人的には、他のテーブルに存在するかどうかを確かめたいだけならEXISTSを使うようにするのがオススメです。

まとめ

  • 他のテーブルの内容を出力する場合のみにJOINを使おう
  • 他のテーブルを検索条件に使用したい場合はEXISTSを使おう

 

基礎からのMySQL 第3版 (基礎からシリーズ)
2012年の改訂版から5年半ぶりの改訂データベースに触れるのが本当に初めてという方に向けて、「データベースとは何か」という基本中の基本から、MySQLを使ったデータベースの作成と操作、PHPとの連携によるWebアプリケーション作成の入り口までを、豊富な図とサンプルにより解説します。5年間で周辺環境が変わりましたので、そちらを一新しての刊行です。

-SQL

© 2024 Shiro's secret base