SQL INNER JOINでテーブルを結合する

INNER JOINでテーブルを結合する

テーブルは通常いくつにも分かれているので、欲しいデータを作るにはそれらを結合する必要がある。結合には4つのやり方があり、 まず最初に「共通部分を取る」INNER JOIN(”いんなーじょん”と読む。日本語だと”内部結合”)について解説する。

この次に解説するLEFT JOINと合わせて使い分けできればかなりカバーできるだろう。

INNER JOINは「共通部分」をとる

会員情報と購買情報の2つのテーブルがあるとしよう。会員情報には会員id・性別・年齢が、購買情報にはidごとにいつ何をどれぐらい買ったかが入っている。

場合によってはそれぞれ独自に集計してスプレッドシートやExcelで集計することもできなくはないが、他にもテーブルをいくつも結合したりすることは起きるしデータ量が大きいとやはりSQLで書く必要があるのでその方法をみてみよう。

サンプルデータ

kiso1_pos,kiso1_customer,kiso1_master,kiso1_heavyの4つを使う。使い方はSQL学習用サンプルデータを参照。

「2つのテーブルを結合する」とはどういうことか

まずkiso1_posを確認する。いつどの商品をいくつ誰が買ったのかのデータだ。もう1つの kiso1_customerは誰が性別は何で何歳かというデータになっている。

2つのテーブルに共通している誰がというデータを使って1つのテーブルにできるれば「いつどの商品をいくつ性別は何で何歳の人が買ったのか」というデータになる。それが「テーブルを結合する」という言葉の意味だ。

INNER JOINの書き方をサンプルデータを使ってみてみる

まずは次のクエリを実行してみよう。

SELECT * FROM kiso1_pos p
INNER JOIN kiso1_customer c
ON p.customer_id = c.id

kiso1_posにさらにkiso1_customerのカラムが追加されているはずだ。

このクエリを日本語で書くと「kiso1_posをpに、kiso1_customerをcと名前を付け付け替え、pのcustomer_idとcのidが同じレコードで結合し、結合したテーブルの全カラムを抽出する」ということになる。

この「pのcustomer_idとcのidが同じレコードで結合」するのがINNER JOINだ。

INNER JOINの書き方

改めてINNER JOINの書き方を整理しよう。

  • SELECTで選択するのは結合した後のテーブルから。「*」で結合したテーブルの全カラム、「テーブル.*」「テーブル.カラム名」でそれぞれ各テーブルの全部と指定したカラム
  • FROM テーブルAに続いて「INNER JOIN 結合するテーブルB」を書く
  • その次に「ON テーブルA.結合キー=テーブルB.結合キー」が続く
  • テーブルの名前の付け替えは基本する。pやcではなくposやcustomerと書く人もいる
  • 結合キーのカラム名は同じでも違っても構わない
  • 結合する2つのテーブルに同じ名前のカラムがあると、1つのテーブルに同じカラムは2つ作れないのでエラーになる。その場合はカラムを除外するか名前を付け替える必要がある。

結合キーが同じ場合のINNER JOIN

もう1つ別のテーブルを結合してみよう。今度はkiso1_heavyをkiso1_posにINNER JOINで結合する。このテーブルにはidではなくcustomer_idが入っているのでテーブル名と合わせて結合キーを変える。

次のクエリを実行してみてほしい。

SELECT * FROM kiso1_pos p
INNER JOIN kiso1_heavy h
ON p.customer_id = h.customer_id

このクエリはエラーになる。

理由を確かめるためにエラーメッセージを確認する。赤丸に白抜きで!のアイコンが画面右側中央にあるのでクリックしてみる。すると「 Duplicate column names in the result are not supported. Found duplicate(s): customer_id 」とメッセージが出てくる。

要するに「同じ名前のカラムは2つは作れない」ということで、今回の場合は結合したテーブルにpのcustomer_idとhのcustomer_idがあり、両方とも結合後のテーブルでは「customer_id」となってしまうためにこのエラーが起きる。

これが上記のINNER JOIN「結合する2つのテーブルに同じ名前のカラムがあると、1つのテーブルに同じカラムは2つ作れないのでエラーになる」状況だ。

なお「なんで会員情報のテーブルがあるのにそれとは別に会員属性でしかもidじゃなくてcustomer_idとカラム名の違うテーブルがあるんだ」と言いたくなるかもしれないが、後で機能が追加されたなどの理由でこういうことは当たりまえに起きる。

INNER JOINのエラーを回避する方法

ではどうしたらエラーを出さずに結合することができるだろうか。両方使ったらエラーになるならばどちらかを使わなければよい、と考えるのは正しい。結合キーが重複している場合は特にそうで、INNER JOINはまったく同じ値が入っているので両方使う必要もない。

SELECT p.* ,h.* except(customer_id) FROM kiso1_pos p
INNER JOIN kiso1_heavy h
ON p.customer_id = h.customer_id

pの全部と、mのcustomer_id以外の全部を取ってくる。これなcutomer_idは重複しないのでエラーにならない。

SELECT p.* , h.flag_heavy FROM kiso1_pos p
INNER JOIN kiso1_heavy h
ON p.customer_id = h.customer_id

もちろん必要なカラムだけを書くこともできる。flag_heavyはmemberにしかないので、m.flag_heavyではなくflag_heavyだけでも大丈夫。

結合キー以外(両方にageのカラムがあるとか)に重複がある場合も同様。ただしテーブルが違うと名前が同じでも微妙に値が違うとか、もしくは全然違う値が入っているということもある。その場合は一方もしくは両方を場外して名前を付け替える。

USING

JOINは結合キーをONで繋ぐが、カラム名が同じ場合はUSINGを使う方法がある。

SELECT * FROM kiso1_pos p
INNER JOIN kiso1_heavy h
USING(customer_id)

INNER JOINでUSINGを使うと結合キーを1つだけしか使わないので、*で全部取ってきてもエラーにならない。

INNER JOINで値がおかしくなる要因

POSデータに属性情報を付与しただけのはずがレコード数がやけに増えている、2つのリストを結合したらあるべきIDが抜けるなど意図していない結果になったらどうするか。まずはその要因をしらなければ直せないのでその説明をしよう。

結合キーのカラムにデータが足りない

このクエリをもう1回実行してみよう。

SELECT p.* ,h.* except(customer_id) FROM kiso1_pos p
INNER JOIN kiso1_heavy h
ON p.customer_id = h.customer_id

エラーにはならないが、結果を見るとレコード数がかなり減っていることに気づくだろう。

次にkiso1_heavyのデータを見てみると13レコードしかないことに気づく。つまりここにはヘビーな会員のIDのみしか存在していないので、INNER JOINだとヘビーな会員に紐づくPOSデータしか残らない。

ヘビーな会員のデータを集計したければこれでよいが、ヘビーな会員とそうでない会員を比較したい、といった場合にはちょっと困る。この場合はPOSデータのレコードは全部残してflag_heavyを結合する方法があり、それは次のLEFT JOINで学ぶ。

その他にも

  • 全会員入っているはずの会員マスタに何らかの理由で欠損がある
  • POSにはデータが入っているが商品マスタが更新されておらずに抜けている
  • 全会員のデータはあるのに購入が無いからPOSにデータが無い

など、どちらかが欠けていることが原因でデータが合わずに困ることはよくある。いくつかの方法があるので都度紹介していく。

結合キーのカラムに重複がある

どちらかのテーブルに結合キーカラムに重複があるとその分増える。また、2つ以上の結合キーでJOINしなければならないのに1つで結合すると重複になる可能性がある。

これはちょっとややこしくなるので別のところで詳しく説明する。

オフィシャルドキュメント

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#inner-join

2020年2月2日基礎1, 未分類

Posted by shinu