SQL 大好き！ 
id:kano-e です！
こないだの FFLT （まだ続いてます！ 3/15 で 16 回目！）で SQL の join について話をしたところ、思ったより好評だった（自画自賛！）ので、記事にまとめました。
「outer join とか innner join とか書き方はわかるんだけど、どういうことかイマイチぴんときてない」
「こういう結果が見たい時に、多分 join を使うんだろうと思うけど、どう書き始めて良いかがわからない」
みたいな悩みに向けて書いています。
join てなんだっけ
join についての話は
inner joinがあってouter joinにはleftとlightがあってfull outer joinもあって- 複数のテーブル（表）から情報をまとめて取得する時に使うよ
みたいな感じで説明されることが多いかなという印象です。
日本語だと「結合」とか言います。
例えばこんな使い方
実際に outer join の使い方を見てみましょう。
users テーブル
id | name ----+---------- 1 | アーサー 2 | カイン 3 | クッキー 4 | トンヌラ (4 rows)
items テーブル
owner_id | name
----------+------------
1 | やくそう
2 | せいすい
3 | やくそう
1 | ぬののふく
(4 rows)
items.owner_id に users.id が入っている、こんなテーブルがあったとします。
この時に、アイテムとその持ち主をまとめて一覧したい場合、こんな感じに left outer join を使います。
select items.name as item , users.name as user from items left outer join users on items.owner_id = users.id ;
結果はこうなります。
item | user ------------+---------- やくそう | アーサー ぬののふく | アーサー せいすい | カイン やくそう | クッキー (4 rows)
right outer join だとこんな感じ。
select items.name as item , users.name as user from items right outer join users on items.owner_id = users.id ;
結果はこう。
item | user
------------+----------
やくそう | アーサー
ぬののふく | アーサー
せいすい | カイン
やくそう | クッキー
| トンヌラ
(5 rows)
今回のデータで見る left と right の違いは、 トンヌラ という item を持たない user が結果に含まれるかどうかになっています。
さて、これで join が使えるようになりました！
……でも、「結合」ってどういうこと？ ってもやもやが残っています。
突然だけど cross join のこと
cross join て普段あんまり使わないし見かけないやつですよね。
でも、ここであえて cross join の話をしたい。
以下のような 2 つのテーブルを用意します。
chars テーブル
name ------ a b c (3 rows)
nums テーブル
name
------
1
2
3
(3 rows)
この 2 つのテーブルを cross join すると SQL はこうなります。
select chars.name as char , nums.name as num from chars cross join nums ;
結果は、こう。
char | num ------+----- a | 1 b | 1 c | 1 a | 2 b | 2 c | 2 a | 3 b | 3 c | 3 (9 rows)
全部の組み合わせが返ってきます。
直積とかも言いますね。
これで cross join も使えるようになった！
……でも、正直「使い道がわからない」ですよね。
もう一度 cross join のこと
最初の users と items の例を cross join でやってみましょう。
users テーブル
id | name ----+---------- 1 | アーサー 2 | カイン 3 | クッキー 4 | トンヌラ (4 rows)
items テーブル
owner_id | name
----------+------------
1 | やくそう
2 | せいすい
3 | やくそう
1 | ぬののふく
(4 rows)
これを素直に cross join してみましょう。
select items.name as item , users.name as user from items cross join users ;
結果はこうなります。
item | user ------------+---------- やくそう | アーサー やくそう | カイン やくそう | クッキー やくそう | トンヌラ せいすい | アーサー せいすい | カイン せいすい | クッキー せいすい | トンヌラ やくそう | アーサー やくそう | カイン やくそう | クッキー やくそう | トンヌラ ぬののふく | アーサー ぬののふく | カイン ぬののふく | クッキー ぬののふく | トンヌラ (16 rows)
想像通り、全部の組み合わせが返ってきました。
でも、こんなの欲しかった結果じゃないですよね。
ここで、今書いた cross join に、こんな where 句を付け加えてみます。
select items.name as item , users.name as user from items cross join users where items.owner_id = users.id ;
そうすると、結果はこう。
item | user ------------+---------- やくそう | アーサー ぬののふく | アーサー せいすい | カイン やくそう | クッキー (4 rows)
めでたく欲しかった結果になりました！！
つまり cross join てなんなの？
全ての join は cross join から考えることができます。
cross join の結果から特定の条件に沿うものを取り出したのが、 inner join や outer join の結果。
直接 cross join してなくても、 join を使ってるならみんな cross join を使っているも同然。
SQL 書くときも cross join から考えるとわかりやすい
と、思ってます。
「全部の組み合わせを作ってから、欲しいものだけを拾う」
ということを考えると
「この結果ってどんな SQL 書いたら良いんだろう」
って時に、役に立ちます。
つまりどういうこと？
- 必要そうな情報があるテーブルを 2 つ用意して
- それらのテーブルの直積をイメージする
- 直積の結果から、必要な情報と不要な情報を仕分けるための条件を考える
条件が出てくれば、あとは left なのか right なのか inner なのか、 on に何を書いたら良いのか、わかるはず！
繰り返しになりますが、全ての join は cross join がベースにあると考えると SQL がイメージしやすくなります。
つまり「結合」とは？
直積でもって全く新しい集合を作るのが「結合」……というのが自分の理解です。
表を二つ並べるイメージで考えてしまうと、「結合」って「こっちの表とこっちの表をくっつける」という足し算的なイメージになってしまいます。
実際には join は足し算ではなく掛け算だとイメージしておくと、 SQL の実行結果にあまりびっくりしなくなります。
まとめ
cross joinを知るとjoinの理解が深まるよ- 「結合」は掛け算で新しい集合を作るよ
おまけ
今回使ったテーブルの作成とデータ投入の SQL を置いておきますので、記事中の SQL を試したい方はどうぞ。
drop table if exists users; create table users (id integer, name varchar); insert into users (id, name) values (1, 'アーサー'), (2, 'カイン'), (3, 'クッキー'), (4, 'トンヌラ'); drop table if exists items; create table items (owner_id integer, name varchar); insert into items (owner_id, name) values (1, 'やくそう'), (2, 'せいすい'), (3, 'やくそう'), (1, 'ぬののふく'); drop table if exists chars; create table chars (name varchar); insert into chars (name) values ('a'), ('b'), ('c'); drop table if exists nums; create table nums (name integer); insert into nums (name) values (1), (2), (3);
