Join

SQL入門の5回目はJoinによる複数のテーブルの結合です。テーブルを結合する場合、

1. 双方のテーブルで、それぞれ結合に使用する列を指定し、
2. その列の値を比較して、
3. 同じ値の行を横につなげる

という手順でテーブルを結合します。このとき左右どちらを基準にするか、値がなかったときにどうするかなど、目的に応じていろいろな結合方法(Left Join, Right Join, Inner Join, …)が用意されています。また後半ではunionを使った縦方向の結合についても説明します。

本記事はFuture Coders独自教材からの引用です。

Joinの文法

まずは結合に使用するJoin句の文法からみてゆきましょう。usingを使用する方法と、onを使用する方法があります。

今回以下のような2つのテーブルを用意します。どちらにもItemIDという列があることに注目してください。

これらを結合する方法をみてゆきます。

双方に同じ列がある場合は以下のようにusingを使用します。

select ... 
from テーブル1
join テーブル2 using(列名)

同じ列名がない場合、それぞれのテーブルの列を明示的に指定します。このとき、テーブルにasを使って省略名を付与するのが一般的です。以下の用にonを使用します。

select ... 
from テーブル1as A
join テーブル2as B on (A.列名 = B.列名)

selectで列名を指定する場合、同じ名前の列が双方のテーブルにある場合は、どちらのテーブルかわからないというエラーになります。その際は、テーブル名を列名の前に記述します。

以下はテーブル名を使って列を特定するSQLの例です。

SELECT A.OrderID, A.ItemID, Name 
FROM `sample.join-orders` as A
join `sample.join-fruits` as B on (A.ItemID = B.ItemID)

Join (inner join)

まずは、2つのテーブルの共通部分のみを結合する方法です。デフォルトでjoinと記述するとinner joinを意味します。

SELECT OrderID, ItemID, Name 
FROM `sample.join-orders` 
join `sample.join-fruits` using(ItemID)

joinの部分を”inner join”に置き換えても同じ結果になります。

結果は以下の通りです。

イメージは以下の通りです。両方のテーブルのItemID列をみると、0，1，2が共通しています。その行のみを抽出して結合しています。

Left Join

左のテーブルを基準にして、左側のテーブルに右側のテーブルを紐づけるときにLeft Joinを使用します。もし合致する値が見つからない場合、nullで埋められます。

SELECT OrderID, ItemID, Name 
FROM `sample.join-orders` 
leftjoin `sample.join-fruits` using(ItemID)

イメージは以下の通りです。左テーブルのItemID列を基準とします。0,1,2,5,6という値があります。0,1,2は右テーブルから値を抽出できますが、5,6はありません。よって、その部分はnullで埋めています。

Right Join

Leftの逆です。右側のテーブルを基準にして結合するときにRight Joinを使用します。もし合致する値が見つからない場合、nullで埋められます。

SELECT OrderID, ItemID, Name 
FROM `sample.join-orders` 
rightjoin `sample.join-fruits` using(ItemID)

イメージは以下の通りです。右テーブルのItemID列を基準とします。0,1,2,3,4という値があります。0,1,2は左テーブルから値を抽出できますが、3,4はありません。よって、その部分はnullで埋めています。

Full Outer Join

Left/Rightは左右どちらかを基準にしていましたが、両方を尊重するのがFull Joinです。Order IDは右のテーブルで0,1,2,5,6で、左は0,1,2,3,4です。全てのケースだと、0,1,2,3,4,5,6となります。このようなテーブルを作成するのがFull Outer Joinです。

SELECT OrderID, ItemID, Name FROM `sample.join-orders` 
fullouterjoin `sample.join-fruits` using(ItemID)

結果は以下の通りです。

Joinのまとめ

いろいろなJoinがでてきたので混乱したかもしれません。ベン図で表現すると以下のようなイメージです。

どちらのテーブルの行を尊重するのか、ということをイメージするときに思い出すとよいでしょう。

他に「交差結合(CROSS JOIN)」という結合方法もあります。これは左右のテーブルの全ての組み合わせを作る方法です。特定の列を基準にするのではないので、onやusingは必要ありません。

SELECT OrderID, A.ItemID as AID, B.ItemID as BID, Name
FROM `sample.join-orders` as A
crossjoin `sample.join-fruits` as B
orderby OrderID, AID, BID

今回Aテーブルが5行、Bテーブルは5行なので、5×5=25行のテーブルが作成されます。

重複行があった場合

前の例では結合する行に重複がありませんでした。今回以下のようなテーブルを使って重複があったときの挙動を確認します。

左のテーブルAと右のテーブルBとの重複関係は以下の通りです。

ID	AとBの対応
1	2 : 1
2	2 : 2
3	1 : 2
4	1 : 0
5	0 : 1

join (inner join)

このテーブルでinner joinを実行すると結果は以下のようになります。

SELECT*FROM `sample.join-A` 
join `sample.join-B` using (Id)

inner joinなので、双方にある行のみが抽出され、4と5の行は含まれていないことが分かります。重複する行がある場合、それらの全ての組み合わせを含んでいます。イメージは以下の通りです。

重複があったときは、それぞれの組み合わせを作って、その結果が行となります。

Left Join

左側のテーブルが基準になるので、Idが4の行が追加されます。それ以外はinner joinと同じ結果になります。

SELECT*FROM `sample.join-A` 
leftjoin `sample.join-B` using (Id)

Right Join

右側のテーブルが基準になるので、Idが5の行が追加されます。それ以外はinner joinと同じ結果になります。

SELECT*FROM `sample.join-A` 
rightjoin `sample.join-B` using (Id)

Full Outer Join

双方のテーブルを含む形になるので、Idが4と5の行が追加されます。それ以外はinner joinと同じ結果になります。

SELECT*FROM `sample.join-A` 
fullouterjoin `sample.join-B` using (Id)

演習

ex-join-basic1.py

join-ordersとjoin-fruitsを結合して、以下のようなテーブルを作成してください。usingを使ってください。 

ex-join-basic2.py

onを使って上記と同じテーブルを作成してください。

ex-join-basic3.py

join-Aとjoin-Bを使って以下のテーブルを作成してください。

ex-join-basic4.py

join-Aとjoin-Bを使って以下のテーブルを作成してください。

ex-join1.py

peopleのnationality列とcountriesのID列を結合して、name_first, name_last, name, capitalからなるテーブルを作成してください。

ex-join2.py

squirrel-dataとpark-dataをPark_IDで連結して1つにしてください。

ex-join3.py

ex-join2の結果をLocationでグループ分けして、Locationが多い順に並べてください。結果にnullが含まれないようにしてください。

ex-join4.py

squirrel-dataとstoriesをPark_IDで結合します。それぞれのテーブルにPark_Nameがありますが、その内容が異なるレコードを表示してください。28件あります。

ex-join5.py

squirrel-dataとstoriesをPark_IDで結合します。それぞれのJOINを行ったレコード数を確認してください。

• inner join ＝391件
• left join ＝433件
• right join ＝396件
• full outer join ＝438件

ex-join6.py

squirrel-dataはリスの個体のデータで、どの公園で観察されたかというPark_ID列があります。一方、storiesはそれぞれの公園の説明なので、1公園1レコードです。データの整合性が取れていれば、どのjoinを行っても結果は同じになるはずです。

ex-join4においてそれぞれのjoinで結果が異なるということは結合で使用した列Park_IDの値が一致しないケースがあったことを意味します。それぞれのテーブルで一致しない行を抽出してください。47件抽出されます。

ex-join7.py

storiesとpark-dataをPark_IDで結合してください。それぞれのテーブルにPark_ID列がありますが、片方にしか存在しない値があります。その値を列挙してください。

解答例

ex-join-basic1.py

SELECT*FROM `sample.join-orders` 
JOIN `sample.join-fruits` using(ItemID)

ex-join-basic2.py

SELECT O.ItemID, OrderID, Name FROM `sample.join-orders` as O 
JOIN `sample.join-fruits` as F on (O.ItemID = F.ItemID)

ex-join-basic3.py

select*from `sample.join-A`
rightjoin `sample.join-B` using(id)

ex-join-basic4.py

select*from `sample.join-A`
fullouterjoin `sample.join-B` using(id)

ex-join1.py

SELECT name_first, name_last, name, capital 
FROM `sample.people` as P
join `sample.countries` as C on (P.nationality = C.id)

ex-join2.py

SELECT*FROM `sample.squirrel-data` as S
join `sample.park-data` as P using(Park_ID)

ex-join3.py

SELECT Location, count(*) as count,  
FROM `sample.squirrel-data` 
join `sample.park-data` using(Park_ID)
groupby Location
having Location isnotnullorderby count desc

ex-join4.py

SELECT S.Park_Name, P.Park_Name
FROM `sample.squirrel-data` as S
join `sample.stories` as P using(Park_ID)
where S.Park_Name != P.Park_Name

ex-join5.py

以下はinner joinの場合です。left join, right join, full outer joinに変えて試してください。

SELECTcount(*) as count
FROM `sample.squirrel-data`
join `sample.stories` using(Park_ID)

ex-join6.py

SELECT S.Park_ID as PID1, P.Park_ID as PID2, *FROM `sample.squirrel-data` as S
fullouterjoin `sample.stories` as P using(Park_ID)
where S.Park_ID isnullor P.Park_ID isnull

ex-join6.py

SELECT S.Park_ID as SID, P.Park_ID as PID
FROM `sample.stories` as S
fullouterjoin `sample.park-data` as P using(Park_ID)
where S.Park_ID isnullor P.Park_ID isnull

Unionによる縦方向の結合

ここまでJoinによる結合をみてきましたが、Joinはテーブルを横方向につなげるイメージでした。一方、同じ列の構造を持つテーブルを縦方向につなげることもできます。この場合はUnionという命令を使用します。

JoinとUnionのイメージを以下に示します。

Unionで結合するテーブルは同じ構造（列の数とデータ型が同じ）である必要があることに注意してください。

table1とtable2を結合するには以下のように記述します。

select ...   --- table1unionallselect ...   --- table2

以下はUnionを使用した例です。join-fruitsとjoin-Aを縦方向に結合しています。

select*from `sample.join-fruits`
unionallselect*from `sample.join-A`

結果は以下の通りです。

join-fruitsとjoin-Aをつなげることにデータ的に意味はありませんが、いずれのテーブルもInteger, Stringという列で構成されているため、unionでテーブルを縦方向につなげることができています。

このUNION ALLはBigQueryの公式ドキュメントの例でもよく利用されます。

例えば、値を横方向に列挙するのであれば、selectの後ろに値を列挙するだけで記述できます。

select "hello" as greet, 30as age, current_date() as today

出力は以下の通りです。

縦方向に並ぶデータを作るときにはUNION ALLを使用します。

SELECT'Tanaka' name, 30 age UNIONALLSELECT'Yoshida', 28UNIONALLSELECT'Suzuki', 35UNIONALLSELECT'Kato', 55

出力は以下の通りです。

列の構造はすべて同じであるという前提があるため、最初の1行分（最初のSelect）で列名を明記すれば、他のSelectでは列名を省略することができます。