【SQL実行文付き】外部結合を超丁寧に解説してみる

内部結合はわかりやすい。

しかし外部結合がどのようなものか忘れてしまう方が多い。

そんな方でもこの記事で絶対に覚えられるよう、
超丁寧に解説してみることにした。

結合、内部結合、外部結合という順番で解説していく。

テーブルの例として以下の2つのテーブルを使う。

book
author

 

database_name=# select * from book;
 id |     name     | author_id
----+--------------+-----------
  1 | 銀河鉄道の朝   |         2
  2 | 川辺のフカフ   |         1
  3 | 1Q3          |         1
  4 | 羅生藻        |         4
(4 rows)
database_name=# select * from author;
 id |     name
----+--------------
  1 | みやざわけん
  2 | 村上春根
  3 | 結婚式部
(3 rows)

テーブルの作り方について知りたいって方は以下のページを参考にしてね。
PostgreSQLをインストール~テーブル作成までの手順を超詳しく解説

なにか記事の内容に誤りがあればコメントで指摘をお願いします。

結合とは

結合とは二つ以上のテーブルを一つにくっつけること。

結合のさせ方の中で特に有名なのが内部結合と外部結合。

この記事ではこの2つを詳しく説明していく。

他にはクロス結合なんてのもある。

結合時のルール

結合にはルールがある。

それが2つのテーブルを結びつける列(カラム)の存在することだ。

今回説明で使うテーブルの例の場合、
bookテーブルのauthor_idカラムとauthorテーブルのidカラムが
2つのテーブルを結びつけるカラムにあたる。

で、この2つのテーブルを結びつけるカラムの値が、
内部結合と外部結合を理解するためには重要だから覚えておいてね。

内部結合とは

内部結合は
「テーブルを結びつけるカラムの値で同一のものが、
結びつけようとしている二つのテーブルのどちらにも存在している行だけを結合させて、
どちらか片方にしかない行は捨てる」という結合方法。

難しいね。深く考えずに例を見てみよう。

具体例

bookテーブルとauthorテーブルを内部結合すると以下のようなテーブルが出来上がる。

database_name=# select * from book inner join author on book.author_id = author.id;
 id |     name     | author_id | id |     name
----+--------------+-----------+----+--------------
  2 | 川辺のフカフ   |         1 |  1 | みやざわけん
  3 | 1Q3          |         1 |  1 | みやざわけん
  1 | 銀河鉄道の朝   |         2 |  2 | 村上春根
(3 rows)
# SQL文の意味:
# 「book.author_idとauthor.idを目印にしてbookテーブルとauthorテーブルを内部結合して、全カラム取得する」

2つのテーブルを結びつけるカラムであるbook.author_idの4はauthor.idカラムに存在せず、
またauthor.idの3はbook.author_idカラムに存在しないため、捨てられている。

author.idに4は存在しないため羅生藻が消え、book.author_idに3が存在しないため結婚式部の列が消えた

ちなみに取り出される時の行の並び順は特に指定をしなければ、
2つのテーブルを結びつけるカラムの昇順になる。

外部結合とは

内部結合は分かりやすいので理解してもらえたと思う。

それでは本題の外部結合だ。

外部結合は
内部結合して出来たテーブルに、
“基準となるテーブル”にしか”2つのテーブルを結びつけるカラムの値”が
存在しない行もくっつけるというやり方。

言葉での説明は分かりにくいので以下の例を見てみよう。

database_name=# select * from book left outer join author on book.author_id = author.id;
 id |     name     | author_id | id |     name
----+--------------+-----------+----+--------------
  2 | 川辺のフカフ   |         1 |  1 | みやざわけん
  3 | 1Q3          |         1 |  1 | みやざわけん
  1 | 銀河鉄道の朝   |         2 |  2 | 村上春根
  4 | 羅生藻        |         4 |    |
(4 rows)

上三行は先ほどの内部結合の結果と同じだが、一行増えている。
これが外部結合による効果。

“基準ではないテーブル”に存在しない値に関しては、
Null(値なし)になっている。

基準となるテーブルは外部結合のさせ方によって変わる。
左外部結合ならSQL文の左の方に書かれているテーブル(今回だとbook)が基準に、
右外部結合ならSQL文の右の方(joinの後)に書かれているテーブルが基準になる。

今回は”left outer join”という形で左外部結合を行なっており、
bookテーブルが基準なためこのような結果になったが、
authorテーブルを基準にした場合は結果が変わる。

database_name=# select * from book left outer join author on book.author_id = author.id;
 id |     name     | author_id | id |     name
----+--------------+-----------+----+--------------
  2 | 川辺のフカフ   |         1 |  1 | みやざわけん
  3 | 1Q3          |         1 |  1 | みやざわけん
  1 | 銀河鉄道の朝   |         2 |  2 | 村上春根
  4 | 羅生藻        |         4 |    |
(4 rows)

ちなみに内部結合の結果は以下のようになる。

database_name=# select * from book inner join author on book.author_id = author.id;
 id |     name     | author_id | id |     name
----+--------------+-----------+----+--------------
  2 | 川辺のフカフ   |         1 |  1 | みやざわけん
  3 | 1Q3          |         1 |  1 | みやざわけん
  1 | 銀河鉄道の朝   |         2 |  2 | 村上春根
(3 rows)

“外部結合 = 内部結合結果 + 基準のテーブルにしかない行”ということがよくわかるね。

まとめ

外部結合を理解するには内部結合から!

完全に別物として考えている人もいるけど、
実はかなり深く関係しているものなんだとわかってもらえたら嬉しいな。