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

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

INNER JOINに続いてLEFT JOINについて説明する。このLEFT JOINとINNER JOINの両方が使えればかなりのことができるようになる。なお、この記事はINNER JOINをすでに一通り読んでいることを前提としている。

LEFT JOINは「左側に追加する」

POSデータと会員情報の2つのテーブルがあるとしよう。POSには会員id・購入日・商品IDなどが、会員情報には会員id・性別・年齢が入っている。

左側にPOS、右側に会員情報があると思った時左側のPOSに右側の会員情報をくっつける。POSではそのままで、会員情報のテーブルにそのidのデータがあればPOSに性別や年齢が追加されるイメージだ。

サンプルデータ

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

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

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

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

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

なんだINNER JOINの時と同じ結果ではないかと気づいた人は正しくて、実は同じ結果になる。それは「 kiso1_posにある会員idとkiso1_customer会員idが一致している」からだ。しかし実際のデータはそんなにうまくできていないことが大半で、その時に違いが出る。

LEFT JOINの書き方

INNER JOINがLEFT JOINに変わるぐらいで書き方は同じなので割愛する。実際にどうなるかはこの後データを使いながら解説する。

LEFT JOINの結果を詳しく見てみる

kiso1_heavyをkiso1_posにLFFT JOINで結合する。次のクエリを実行してみてほしい。

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

このクエリはやっぱりエラーになる。INNER JOINの時と同じで結合したテーブルにpのcustomer_idとhのcustomer_idがあり、両方とも結合後のテーブルでは「customer_id」となってしまうためにこのエラーが起きる。エラーを回避する方法も同じだ。

SELECT p.* ,h.* except(customer_id) FROM kiso1_pos p
LE
FT JOIN kiso1_heavy h
ON p.customer_id = h.customer_id
ORDER BY customer_id

pの全部と、mのcustomer_id以外の全部を取ってくる。これならcustomer_idは重複しないのでエラーにならない。今度はcustomer_idで並び変えもしておこう。さて、結果はどうなっているか。

customer_id=1,2の場合flag_heavyに1が入っている。3以降はNULLになり5ではまた1になる。つまり、 kiso1_heavyにidがあれば1、なければNULLになることが判るだろう。

もうちょっと詳しく見るために全カラムを表示しよう。hのcustomer_idはh_customer_idと名前を変えておく。つまり次のクエリだ。

SELECT p.* , h.customer_id as h_customer_id , h.flag_heavy FROM kiso1_pos p
LEFT JOIN kiso1_heavy h
ON p.customer_id = h.customer_id

INNER JOINの場合は両方に存在しないcustomer_id=3は消えてしまったが、LEFT JOINの場合は残る。これが最大の違いだ。つまりはkiso1_posを左側、kiso1_heavyを右側とみて、「左側のkiso1_posに対して右側のkiso1_heavyを追加する」のがLEFT JOINということになる。

そして左側に結合キーが存在しない場合は追加するデータがないということなので、NULLが入る。

USING

ちなみにUSINGの挙動もINNER JOINと同じ。

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

ただしLEFT JOINの場合はIDだけを結合する場合もあり(その後にCASEを使ってフラグにしたりする)USINGを使ってしまうと元のテーブルしか出てこないので注意。

LEFT JOINで意図しない結果になった場合の要因と対策(両方に重複が無い場合)

JOINしたら意図していない結果になってしまった時のためにLEFT JOINする両方のテーブルで結合キーがどういう状況にあると何が起きるかについてまとめておく。まずは両方のテーブルに結合キーでの重複がない場合を考えよう。結合キーはcustomer_idとしておく。

2つのテーブルでcustomer_idが一致する

この場合は1対1なので増えることはあり得ないし減ることもない。INNER JOINと同じ結果になるのはすでに見た。

結合される側にあるcustomer_idが結合する側にない

両方に重複は無いがcustomer_idが一致しない場合はさらに3つに分かれるがそのうちの1つは結合される側にあるcustomer_idが結合する側にない場合だ。

この場合は結合した後に結合する側にないcustomer_idの部分はNULLになる。それ以外は影響しない。

結合される側にないcustomer_idが結合する側にある

2つ目は結合される側にないcustomer_idが結合する側にだけある場合だが、LEFT JOINはあくまでも結合される側が残るだけなのでやはり結果には出てこない。

何らかのエラーで欠損したり、会員登録はしたが指定した期間に購入が無いなどで起きやすい。

結合される側と結合する側でお互い違うcustomer_idが存在する

例えばkiso1_posをどこか1週間で絞りこんだデータとkiso1_heavyではお互いに存在しないcustomer_idが出てくる可能性は大いにありえる。

この場合でも結合される側に無ければ無視されるのであとは「結合される側にあるcustomer_idが結合する側にない」と同じで結合できなければNULLになる。

いろいろ処理をしていると知らない間に発生したりする。もしも完全な会員idリストが必要な場合(これもよくある)はLEFT JOINだけでは足りない。いくつか方法はあるので別のところで解説する。

LEFT JOINで意図しない結果になった場合の要因と対策(どちらか一方か両方に重複がある場合)

LEFT JOINで結合するテーブルのどちらかないしは両方に重複があると、「POSに属性を付与したらレコード数がすごいことになった」のように意図的でないのに増えてしまうことがある。

原因は結合キーに重複があることなのだが、ではどちらのテーブルに重複があるとこうなるかを考えてみよう。

結合される側に重複があっても結合する側に重複がなければレコード数は変わらない

もし結合される側のテーブルでcustomer_idが重複しても結合する側に重複がなければレコード数は変わらない。結合される側のidに一致するレコードにカラムが追加されるだけだ。

解説で使ったkiso1_posとkiso1_heavyのケースはこれ。

結合される側に関係なく、結合する側に重複があればレコード数は増える

もし結合する側にcustomer_id=1が2レコードあったとする。結合される側にcustomer_id=1が1つあると、結合する側のcustomer_id=1の1レコードに対して結合される側のcutomer_id=1が結合するので、結果は2レコードになる。

結合される側 結合する側 
customer_id customer_idflag
111
  11

つまり「LEFT JOINした結果元のテーブルよりもレコード数が増えたなら、結合する側の結合キーに重複がある」ということになる。

問題にすべきはそれが意図した結果かどうかなので、このような状況ではこうなるということを理解しておいて目的に合ったやり方が選べるようになっておこう。

RIGTH JOIN

LEFTがあるならRIGHTもありそうだと思うだろうがその通りでRIGHT JOINも存在する。しかし

  • LEFT JOINと書き方は全く同じでテーブルの順番が違うだけなので両方使えるようにする意味があまりなくどちらかに統一する方がいい
  • 個人的にはLEFT JOINの方が判りやすい
  • 今まで他の人が使っているのを見た記憶がない

ややこしくなるだけでメリットを感じないので紹介に留める。

LEFT [OUTER] JOIN

最後に名称について。LEFT JOINは正式にはLEFT OUTER JOINと書き、日本語だと「左外部結合」と言う。クエリではOUTERは省略可能。左にあるテーブルに外から別のデータをくっつける、ぐらいで覚えておけばよいと思うがみんな大体「LEFT JOIN」なのでそういう言い方もある、ということだけ触れておく。

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

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

2020年2月11日基礎1

Posted by shinu