💽

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

2022/12/11に公開約9,100字

これは ミライトデザイン 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 一発でやれや」って言われちゃうわけですね。

基本文法

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

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

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

そもそも左と右とは

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

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

略していいのは

leftright は略せません。

innerouter は略していいです。

あとでベン図を見れば一発でわかりますが、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.' );

ケース別サンプル

淡々と見ていきます。

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

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.

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

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

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

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

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

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

再掲
 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.

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

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

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

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

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

left joinleft 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.

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

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

2. 左に向かって列を広げます ( このとき欠ける部分は 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 に似ています。

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

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

3. 左右に向かって列を広げたものが結果です ( 欠ける部分は 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 だけにある行が知りたければ、左外部結合をしてから内部を除外すればいいはずです。

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

2. こうなるはず

やってみましょう。

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 を理解しやすいかもしれません。 ↩︎

Discussion

ログインするとコメントできます