ケース別ゆるふわテーブル結合

これは ミライトデザイン Advent Calendar 2022 の 11 日の記事です。

前日は polidog@PartyHard Inc. さんの CSS in JS と Stitches について でした。

今年もミライトアドカレで polidog さんの記事が読めて嬉しいです、ありがとうございました！

記事の翌日紹介でスーパープログラマなんて言われちゃった、やったね♪
なんて喜んでもいられず、今年は得意でもない DB について「ゆる〜い Join の記事」と「ちょっとマジメな Index の記事」を書くんですが、ゆる〜い気持ちで備えていた今日のハードルが不意打ち気味にブチ上がっています。

みなさん、Join は得意ですか？

「どうも join がいつもすぐ書けんのう」
「left だの inner だのと、細かいことは知らんぞい」
「どのワードが略せるとか略すとどうなるとか、覚えられる気がせんぞい」

という方いませんか？

僕は結構長いこと適当に誤魔化してました。

left で動いてるクエリになんとなく outer とか書いてみたりして。
「結果かわんねーじゃんッ！」とかやったりしませんか？

毎回適当に調べてみるんだけど、サンプルデータとかサンプルクエリってなんか頭に入ってこないんですよね。

そんなこんなで僕はずっと「よくわからんなー」って思ってたんですけど、前に急にベン図が浮かんで一発で即理解できたことがあったので、そのときの感覚を共有してみたいと思います。

ということで超簡単にケース別に整理してみます。

前提

ケース別のクエリとベン図を見る前に、簡単な基礎知識の整理をしておきます。

テーブルを結合する理由

結合は 複数のテーブルからまとめて情報を取得する ときに使います。

例えばなんらかのプログラミング言語でサービスを作っていて、ユーザテーブル と 購入履歴テーブル をくっつけて 全ユーザの購入履歴 を作りたいとします。

テーブルが別な以上、絶対どこかのタイミングで 2 つのテーブルを紐づける必要があります。

2 つのテーブルを紐づけるサンプルコードを、細かいことは度外視して 2 例考えます。

↓ は「ユーザテーブルから取得」して、その結果で「購入履歴テーブルから取得」しています。

$users = DB.run('select * from user')
for $users -> $user {
  $items = DB.run('select * from item where user_id = $user.id')
  for $items -> $item {
    print($user, $item)
  }
}

↓ は「ユーザテーブルと購入履歴テーブルを結合してから取得」しています。

$items_by_users = DB.run('select * from user join item on user.id = item.user_id')
for $items_by_users -> ($user, $items) {
  for $items -> $item {
    print($user, $item)
  }
}

上のコードの方が、「あれして、それからこれして」って思いついた思考の順番通りに書けるし、join を使わなくていいし、ただの二重ループなので気持ちが楽です。

でも ユーザの数だけ購入履歴テーブルへのアクセスが必要 です。
ユーザが 10,000 いたら 10,001 回の DB 処理が発生します。ヤベェ。

下のコードだと DB 処理は 1 回ですね。

調べるテーブルの数も結果の行数も同じですが DB に接続する回数が圧倒的に違います。

なので「いやおめぇ、join 一発でやれや」って言われちゃうわけですね。

基本文法

from と join でテーブルを列挙して、on で結合条件を書きます。

select ~
from [table1]
join [table2] on xxx = yyy

この記事では一番シンプルなこのスタイルのみ整理します。基礎にはこれで十分です。

そもそも左と右とは

from に書いた方が 左 で、join でくっつける方が 右 です。

on xxx = yyy の順番は関係ありません。

略していいのは

left や right は略せません。

inner と outer は略していいです。

あとでベン図を見れば一発でわかりますが、inner に左右というものはないので、左右の指定は outer のときに限ります。

つまりありえるパターンは次のどれかです。^[1]

種類	略記	同じ
内部結合	join	inner join
左外部結合	left join	left outer join
右外部結合	right join	right outer join
完全外部結合	full join	full outer join

ベン図を見て 略すと意味が変わってしまうところ と 書かなくても決まるところ が抑えられると、略記は一発で理解できます。

この記事の動作確認をするには

適当にデータベースとテーブルを用意します。
MySQL だと Full Outer Join だけできないので、それが気になる人は PostgreSQL にするといいと思います。

DB Fiddle というサイトを使うと、好きな RDBMS を選んでテーブル作成とクエリ実行をブラウザだけで試せます。

https://www.db-fiddle.com/

手元でさくさく実行したい人は Docker を使うといいと思います。

Docker を使う例

コンテナ起動

$ docker run -it --name db -e POSTGRES_USER=foo -e POSTGRES_PASSWORD=foo -e POSTGRES_DB=foo postgres:11-alpine

接続

# 別のターミナルタブで
$ docker exec -it db psql -h localhost -U foo -d foo

コンテナ終了

$ docker rm -f db
# もしくは起動したタブで Ctrl + c

これから使うスキーマ

create table A ( user_id int, message varchar(20) );

insert into A ( user_id, message ) values ( 1, 'a - 1st.' );
insert into A ( user_id, message ) values ( 2, 'a - 2nd.' );

create table B ( user_id int, message varchar(20) );

insert into B ( user_id, message ) values ( 2, 'b - 2nd.' );
insert into B ( user_id, message ) values ( 3, 'b - 3rd.' );

ケース別サンプル

淡々と見ていきます。

テーブルとデータは次の通りです。
無理に意味は見出さず、シンプルに A と B でいきます。

select * from A;

 user_id | message
---------+----------
       1 | a - 1st.
       2 | a - 2nd.

select * from B;

 user_id | message
---------+----------
       2 | b - 2nd.
       3 | b - 3rd.

A と B にある: 内部結合 ( [ inner ] join )

A と B にあるということは、こんな感じです。

なるほど、これは確かに 左右の指定はないのも納得できそう です。
内部って感じもします ね。

select *
from A
JOIN B on A.user_id = B.user_id;

 user_id | message  | user_id | message
---------+----------+---------+----------
       2 | a - 2nd. |       2 | b - 2nd.

結合するときの動作をイメージしてみます。

ベン図で考えるのはいいんですけど、円だとちょっとイメージが合いにくいので、四角いベン図にします。

１. まず 2 テーブルに 2 行ずつあるので、2 x 2 の総当たり 4 組み合わせをチェックします

２. 一致するところがありました

３. 左右に向かって列を広げたものが結果です

図と結果が同じ形をしてますね。

再掲

 user_id | message  | user_id | message
---------+----------+---------+----------
       2 | a - 2nd. |       2 | b - 2nd.

左右の指定がない join とだけ書くやつは、こんなイメージがあると一発で理解できます。

A にあればいい: 左外部結合 ( left [ outer ] join )

A にあればいいということは、こんな感じです。

なるほど、これは確かに 左右の指定が必須なのも納得できそう です。
さっきのを内部とするなら、外部って感じもします ね。

select *
from A
LEFT JOIN B on A.user_id = B.user_id;

 user_id | message  | user_id | message
---------+----------+---------+----------
       1 | a - 1st. |         |
       2 | a - 2nd. |       2 | b - 2nd.

こいつも流れをイメージしてみます。

１. 左 ( from にした方 ) のテーブルを全部見て

２. 右に向かって列を広げます ( このとき欠ける部分は Null になる )

図と結果が同じ形をしてますね。

再掲

 user_id | message  | user_id | message
---------+----------+---------+----------
       1 | a - 1st. |         |
       2 | a - 2nd. |       2 | b - 2nd.

left join は left outer join だってことがベン図でわかっていれば「left 付けたら欠ける部分もあるよね」ってのが理解できますね。

B にあればいい: 右外部結合 ( right [ outer ] join )

これは left の逆 です。

select *
from A
RIGHT JOIN B on A.user_id = B.user_id;

 user_id | message  | user_id | message
---------+----------+---------+----------
       2 | a - 2nd. |       2 | b - 2nd.
         |          |       3 | b - 3rd.

流れもさっきと逆なだけです。

１. 右 ( join にした方 ) のテーブルを全部見て

２. 左に向かって列を広げます ( このとき欠ける部分は Null になる )

図と結果が同じ形をしてますね。

再掲

 user_id | message  | user_id | message
---------+----------+---------+----------
       2 | a - 2nd. |       2 | b - 2nd.
         |          |       3 | b - 3rd.

A か B にあればいい: 完全外部結合 ( full [ outer ] join )

A か B にあればいいということは、こんな感じです。

なるほど、これは確かに 左右の指定はないのも納得できそう です。
両方の外部だと思うと 完全外部って感じもします ね。

select *
from A
FULL JOIN B on A.user_id = B.user_id;

 user_id | message  | user_id | message
---------+----------+---------+----------
       1 | a - 1st. |         |
       2 | a - 2nd. |       2 | b - 2nd.
         |          |       3 | b - 3rd.

処理の流れは inner join に似ています。

１. まず 2 テーブルに 2 行ずつあるので、2 x 2 の総当たり 4 組み合わせをチェックします

２. 全部の行において ( ここが join と違う )

３. 左右に向かって列を広げたものが結果です ( 欠ける部分は Null になる )

図と結果が同じ形をしてますね。

再掲

 user_id | message  | user_id | message
---------+----------+---------+----------
       1 | a - 1st. |         |
       2 | a - 2nd. |       2 | b - 2nd.
         |          |       3 | b - 3rd.

MySQL における完全外部結合

MySQL は full が指定できません。

が、ベン図を見ればおわかりでしょう、左外部結合 + 右外部結合 - 真ん中が同値ですね。
( union は重複を除外します )

select *
from A
LEFT JOIN B on A.user_id = B.user_id
UNION
select *
from A
RIGHT JOIN B on A.user_id = B.user_id;

 user_id | message  | user_id | message
---------+----------+---------+----------
       1 | a - 1st. |         |
       2 | a - 2nd. |       2 | b - 2nd.
         |          |       3 | b - 3rd.

A だけにある: 左外部結合の応用

確認を兼ねて、1 つ応用です。

A だけにある行が知りたければ、左外部結合をしてから内部を除外すればいいはずです。

１. 左外部結合して、内部を除外すると

２. こうなるはず

やってみましょう。

select *
from A
LEFT JOIN B on A.user_id = B.user_id
where b.user_id is null;

 user_id | message  | user_id | message
---------+----------+---------+---------
       1 | a - 1st. |         |

できましたね。

ちなみに条件を間違えて is not null にすると、inner join と同じになりますね。

おしまい

四角いベン図、よくないですか？

これだけわかってればもう楽勝ですね。

15 日には Index の Book も出す予定ですので、そちらもぜひ読んでみてください！

出ましたー
https://zenn.dev/suzuki_hoge/books/2022-12-database-index-9520da88d02c4f

明日は ariaki さんです。

ariaki さんはいろいろなことに深い造詣をお持ちの方なので、どんな内容か楽しみです！

脚注

1. この記事では cross には触れません。が、cross の挙動を一度目で見てみると inner join を理解しやすいかもしれません。