内部結合と外部結合

内部結合と外部結合ってどう違うのか、を調べたのでまとめていきます。
実際に、SQL操作をしながらやっていきます。

DB 準備

DB は PostgreSQL を利用します。

テーブルを二つ用意します。
記事 (articles) と カテゴリー (categories) テーブルです。

CREATE TABLE categories (
  id SERIAL NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL
);

CREATE TABLE articles (
  id SERIAL NOT NULL PRIMARY KEY,
  title varchar(255) NOT NULL,
  category_id integer
);

テーブルにデータを入れます。

INSERT INTO categories (name)
VALUES
  ('日常'),
  ('技術'),
  ('ポエム'),
  ('政治');

INSERT INTO articles (title, category_id)
VALUES
  ('一月一日', 1),
  ('Ruby on Rails 入門', 2),
  ('北海道行った話', 1),
  ('じんせい', 3),
  ('DNSを理解した', 2);

中身は以下の通りです。

categories

id name
1 日常
2 技術
3 ポエム
4 政治

articles

id title category_id
1 一月一日 1
2 Ruby on Rails 入門 2
3 北海道行った話 1
4 じんせい 3
5 DNSを理解した 2
6 これ何 5

内部結合

まず、結合とは、複数のテーブルを組み合わせることです。
そして、結合するとき、多くの場合は条件を与えます。

そして、内部結合とは組み合わせる両方のテーブルに対象のレコードが存在する場合に限り、それらを組み合わせて、出力するということになります。

PostgreSQL では、内部結合は INNER JOIN ~ ON ・・・ となります。
~の部分が組み合わせるテーブル名、ONの後ろの・・・部分が条件となります。

実際に、準備でつくった categories テーブルと articles テーブルを使って試してみます。

SELECT *
FROM articles
INNER JOIN categories
  ON category_id = categories.id;

結果は以下の通りになります。

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
(5 rows)

これを見てわかると思いますが、片方のテーブルにしかないレコードは表示されていません。

例えば、categories テーブルのレコード (id, name) = (4, '政治') は、この idcategory_id にもつレコードが articles テーブル内にないため、結合結果に表れません。
articles テーブルのレコード (id, title, category_id) = ( 6, 'これ何', 5) も同様です。

外部結合

内部結合の次は外部結合です。
内部結合・外部結合の名前や、内部結合の説明から予想できているかもしれませんが、外部結合とはどちらか片方にレコードがあると、結合結果に出力されます。

ただし、どちらか一方というと、

  1. categoreis テーブルのみに表れるレコードはすべて
  2. articles テーブルに表れるレコードはすべて
  3. categories テーブル、または articles テーブルに表れるレコードすべて

と、3パターンがあります。

ということで、外部結合は3パターンあります。

  1. LEFT OUTER JOIN
  2. RIGHT OUTER JOIN
  3. FULL OUTER JOIN

ここからは具体例を見ていきます。 LEFT JOINRIGHT JOINFULL JOINの順に、SQL文とその結果を続けて載せます。

LEFT JOIN

SQL

SELECT *
FROM articles
LEFT JOIN categories
  ON category_id = categories.id;

結果

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
  6 | これ何             |           5 |    |
(6 rows)

RIGHT JOIN

SQL

SELECT *
FROM articles
RIGHT JOIN categories
  ON category_id = categories.id;

結果

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
    |                    |             |  4 | 政治
(6 rows)

RIGHT JOIN

SQL

SELECT *
FROM articles
FULL JOIN categories
  ON category_id = categories.id;

結果

 id |       title        | category_id | id |  name
----+--------------------+-------------+----+--------
  1 | 一月一日           |           1 |  1 | 日常
  2 | Ruby on Rails 入門 |           2 |  2 | 技術
  3 | 北海道行った話     |           1 |  1 | 日常
  4 | じんせい           |           3 |  3 | ポエム
  5 | DNSを理解した      |           2 |  2 | 技術
  6 | これ何             |           5 |    |
    |                    |             |  4 | 政治
(7 rows)

INNER JOIN との違いは、INNERの部分がそれぞれ、LEFTRIGHTFULL にかわったことです。

LEFT、RIGHT、FULLの違いはそれぞれどちらのテーブルに存在する場合に、結合するかということです。
そして、FROM の後ろが左のテーブル、JOINの後ろが右のテーブルとして考えます。
LEFTなら左、RIGHTなら右のテーブルのレコードは、もう片方のテーブルに存在しない場合でも、結合します。
FULLの場合だと、左か右のどちらかに存在すれば、結合します。

終わり