内部結合と外部結合
内部結合と外部結合ってどう違うのか、を調べたのでまとめていきます。
実際に、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, '政治')
は、この id
を category_id
にもつレコードが articles テーブル内にないため、結合結果に表れません。
articles テーブルのレコード (id, title, category_id) = ( 6, 'これ何', 5)
も同様です。
外部結合
内部結合の次は外部結合です。
内部結合・外部結合の名前や、内部結合の説明から予想できているかもしれませんが、外部結合とはどちらか片方にレコードがあると、結合結果に出力されます。
ただし、どちらか一方というと、
- categoreis テーブルのみに表れるレコードはすべて
- articles テーブルに表れるレコードはすべて
- categories テーブル、または articles テーブルに表れるレコードすべて
と、3パターンがあります。
ということで、外部結合は3パターンあります。
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
ここからは具体例を見ていきます。
LEFT JOIN
、RIGHT JOIN
、FULL 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
の部分がそれぞれ、LEFT
、RIGHT
、FULL
にかわったことです。
LEFT、RIGHT、FULLの違いはそれぞれどちらのテーブルに存在する場合に、結合するかということです。
そして、FROM
の後ろが左のテーブル、JOIN
の後ろが右のテーブルとして考えます。
LEFTなら左、RIGHTなら右のテーブルのレコードは、もう片方のテーブルに存在しない場合でも、結合します。
FULLの場合だと、左か右のどちらかに存在すれば、結合します。
終わり