【MySQL】テーブル・クエリの結合に関する構文の覚え書き(inner/outer join , unionなど)

MySQL勉強中につき、テーブル結合に関しての覚書です。

 

①そもそもなぜテーブルを分けているケースが多いのか

 

テーブルなんて最初から全部ひとつにくっつけておけばいいじゃないか、そうしたらわざわざ結合しなくてもいいし、、

なんて思ったのですが、テーブルを分けることには以下のようなメリットがあります。

 

〇重複をなくしてテーブルのデータ量を削減できる

購買履歴テーブルと、商品テーブルがあるとします。

購買履歴テーブルには時系列ごとにユーザーと商品IDが格納されていて、商品テーブルには商品IDごとに商品名を紐づけているとします。

仮に購入履歴テーブルに商品名のカラムも持っておくとするならば、商品名に重複が発生します。

それをテーブル2つにわけることで、商品IDは重複するものの、商品名は重複がなくなります。

商品名より商品IDのほうがデータ量が小さいですから、何万件、何十万件なんてたくさん行があったときに総データ量に大きな差が生まれます。

 

〇テーブルの修正が楽になる

同様に購買履歴テーブルと、商品テーブルがあるとします。

ここで、急に商品Aの名前を変更しなければいけない、となったとすると、このようにテーブルを分けておけば、商品テーブルの商品Aの箇所を修正するだけで済みます。(1か所しか直さなくてよい。)

これが、仮にマスタとして商品テーブルに分けていなかったならば、購買履歴テーブルにあるすべての商品Aの箇所を修正しなくてはいけません。これは面倒です。

 

②JOINはテーブルを横にくっつける

まずはテーブルとテーブルの結合です。これはJOINを使い、種類としては

・inner join (join)

・left outer join (outer join) / right outer join

・cross join

の3つがあるとのこと。MySQLでなければ full outer joinなんてものもあるらしいけれど、今回はMySQLに限るので無視します。

↑のようなテーブルA,Bがあるとします。

 

inner joinは共通項のみくっつける

inner joinは2つのテーブルの共通項のみをくっつけるイメージです。

構文としては

select 任意のカラム from テーブルA

(inner) join テーブルB on テーブルA.カラム名 = テーブルB.カラム名

※innerは省略可

です。

on テーブルA.カラム名 = テーブルB.カラム名のカラム名は結合のキーとするカラムを選択します。

↑の例では、テーブルAとテーブルBは hit_id をキーにしてくっつけるので、

on テーブルA.hit_id = テーブルB.hit_id

となります。

そうすると、↓のキャプチャのようにテーブルAとBで共通する行のみがくっついて出力されます。

本来テーブルAは6行ありましたが、テーブルBと共通する行が2つしかなかったため、それだけ出力されました。

 

left/right outer joinはすべてくっつける

outer join は inner join と異なり、共通項だけでなく全部くっつけてくれます。

left outer join と right outer join にわかれますが、考え方は両方とも同じです。

構文は

select 任意のカラム from テーブルA

left (outer) join テーブルB on テーブルA.カラム名 = テーブルB.カラム名

※outerは省略可

出力結果は以下のようになります。

先ほどとは異なり、テーブルAとBで共通していない行もみな出力されました。データが空っぽの箇所にはかわりにnullが入ります。

leftとrightの使い分けは、左側右側どちらのテーブルを軸にしてくっつけるかの違いです。

(左側、とはfromで指定するテーブルです)

left joinを使えば、fromで指定したテーブルのデータを取得して、それからキーとなるカラムと合致するものをもう一方のテーブルから見つけてくっつけます。

right joinを使えば、fromで指定していない方のテーブルに対して、キーとなるカラムと合致するものをもう一方のテーブルから見つけてくっつけます。

 

cross joinは全パターンの組み合わせを表示する

実務ではほぼ使わないそうですが、一応cross joinというものもあります。

構文は

select 任意のカラム from テーブルA

cross join テーブルB

めっちゃシンプル。

cross joinはテーブルAそれぞれの行に対して、テーブルBの各行をクロスさせ、全パターンの組み合わせを表示します。

つまり、以下のような出力結果が得られます。

もうキーとか関係なしに、手当たり次第にクロスさせていくイメージです。

 

③UNIONはクエリを縦にくっつける

joinがテーブルを横にくっつけるものであったのに対して、2つのテーブルを縦にくっつけるのがUNIONです。

こんなテーブルA,Bがあったとします。

unionは重複を削除する

unionを使えば、テーブルAのしたにテーブルBをくっつけることができます。

構文は

(テーブルAのクエリ)

union

(テーブルBのクエリ)

です。

unionと書くと、その後ろにdistinctが隠れていて、テーブルAとテーブルBで重複を削除してくれます。

出力結果は以下の通り。(左側)

 

union allは重複を削除しない

unionの後ろにallをつけることで、重複を削除せず全部くっつけることができます。

上のキャプチャの右側のような出力結果を得られます。

テーブルAとテーブルBでは「hit_id=4」の行が重複していますが、unionではそれがくっついても1行分しか表示されておらず(=重複が削除されており)、union allでは2行分表示されています。

 

おわり