複数テーブルを結合する時に使う、Inner JoinとLeft Join (Left Outer Join)とRight Join(Right outer Join)の違いをさらっと説明します。
動作環境
SQL FIDDLEでMySQL5.6、PostgreSQL9.6で動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。
Inner JoinとOuter Joinの違い
Inner Joinは内部結合と呼ばれており、Outer Joinは外部結合と呼ばれています。
Inner Joinは結合時に指定した値が２つのテーブルに存在している場合、項目として表示されます、
それに対してOuter Joinはどちらかのテーブルを基準としてない値はnullとして表示します。
Outer JoinにはLeft outer joinと Right Outer Joinがあります。
Left Outer Joinの方が使用頻度が高いです。（Right Joinはあまりみかけない。）
構文
○構文
SELECT columnA, columnB … FROM tableA INNER JOIN tableB ON rule
○実際に書いた場合
SELECT
itemid,
itemname
FROM
item
INNER JOIN
stockControl
on
item.ItemID = stockControl.ItemID
order by
item.itemId
使用するテーブル
以下のテーブルを使用します。テーブルは2つあります。
| Itemid | itemname | quantity | area |
|---|---|---|---|
| 2 | プリン | 25 | 大阪 |
| 3 | ゼリー | 30 | 東京 |
| 4 | ティラミス | 15 | 大阪 |
| 5 | ヨーグルト | 30 | 東京 |
| 6 | ゼリー | 20 | 大阪 |
| itemid | itemname | stock | arrivedate |
|---|---|---|---|
| 1 | ゼリー | 40 | 2020-04-20 |
| 2 | プリン | 65 | 2020-04-20 |
| 4 | ティラミス | 40 | 2020-04-22 |
| 5 | ヨーグルト | 10 | 2020-04-20 |
itemテーブルにはItemID 1 が stockControlテーブルにはItemID 3 と 6から始まる行が存在していないことがInner JoinとLeft Joinの違いを知る上では重要です。
準備
上記テーブルを作成するコードです。実際に動きを見たい方は活用ください。
— CREATE文 item テーブル
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20),
Quantity numeric(8, 0),
AREA character varying (20),
primary key(ItemID)
);
— itemテーブル用INSERT文
insert into item
values(0002, ‘プリン’, 25, ‘大阪’),
(0003, ‘ゼリー’, 30, ‘東京’),
(0004, ‘ティラミス’, 15,’大阪’),
(0005, ‘ヨーグルト’, 30, ‘東京’),
(0006, ‘ゼリー’, 20, ‘大阪’);
— CREATE文 stockControl テーブル
CREATE TABLE stockControl (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20),
Stock numeric(8, 0),
ArriveDate DATE,
primary key(ItemID)
);
— stockControlテーブル用INSERT文
insert into stockControl
values(0001, ‘ゼリー’, 40, ‘2020-04-20’),
(0002, ‘プリン’, 65, ‘2020-04-20’),
(0004, ‘ティラミス’, 40, ‘2020-04-22’),
(0005, ‘ヨーグルト’, 10, ‘2020-04-20’);
それでは実際に書いていきます。Itemテーブルを軸に書いていきます。
Inner Joinでテーブルを結合
ItemIdを結合の条件に2つのテーブルをInner Joinで結合します。
SELECT
*
FROM
item
inner join
stockControl
on
item.ItemID = stockControl.ItemID
order by
item.itemId
■ 結果
| itemid | itemname | quantity | area | itemid | itemname | stock | arrivedate |
|---|---|---|---|---|---|---|---|
| 2 | プリン | 25 | 大阪 | 2 | プリン | 65 | 2020-04-20 |
| 4 | ティラミス | 15 | 大阪 | 4 | ティラミス | 40 | 2020-04-22 |
| 5 | ヨーグルト | 30 | 東京 | 5 | ヨーグルト | 10 | 2020-04-20 |
itemテーブルにはItemID 1 が stockControlテーブルにはItemID 3 と 6から始まる行が存在しないため結合時に削除されていることがわかります。
Left (outer) Joinでテーブルを結合
ItemIdを結合の条件に2つのテーブルをLeft Joinで結合します。(outerは省略可)
SELECT
*
FROM
item
left join
stockControl
on
item.ItemID = stockControl.ItemID
order by
item.itemId
■ 結果
| itemid | itemname | quantity | area | itemid | itemname | stock | arrivedate |
|---|---|---|---|---|---|---|---|
| 2 | プリン | 25 | 大阪 | 2 | プリン | 65 | 2020-04-20 |
| 3 | ゼリー | 30 | 東京 | (null) | (null) | (null) | (null) |
| 4 | ティラミス | 15 | 大阪 | 4 | ティラミス | 40 | 2020-04-22 |
| 5 | ヨーグルト | 30 | 東京 | 5 | ヨーグルト | 10 | 2020-04-20 |
| 6 | ゼリー | 20 | 大阪 | (null) | (null) | (null) | (null) |
itemテーブルにはItemID 1 がないためstockControlテーブルのItemID 1は結合できず削除。stockControlテーブルにはItemID 3 と 6から始まる行が存在しないため結合時にnullで表示されていることがわかります。
左側に結合時にnullを作成しない状態。
Right (outer) Joinでテーブルを結合
ItemIdを結合の条件に2つのテーブルをRight Joinで結合します。(outerは省略可)
SELECT
*
FROM
item
Right join
stockControl
on
item.ItemID = stockControl.ItemID
order by
stockControl.itemId
■ 結果
| itemid | itemname | quantity | area | itemid | itemname | stock | arrivedate |
|---|---|---|---|---|---|---|---|
| (null) | (null) | (null) | (null) | 1 | ゼリー | 40 | 2020-04-20 |
| 2 | プリン | 25 | 大阪 | 2 | プリン | 65 | 2020-04-20 |
| 4 | ティラミス | 15 | 大阪 | 4 | ティラミス | 40 | 2020-04-22 |
| 5 | ヨーグルト | 30 | 東京 | 5 | ヨーグルト | 10 | 2020-04-20 |
itemテーブルにはItemID 1 がないがstockControlテーブルには値があるため結合時にnullで表示。stockControlテーブルにはItemID 3 と 6から始まる行が存在しないため結合時に削除されていることがわかります。
右側に結合時にnullを作成しない状態。
Outer Joinはどちら側を優先的に表示したいかでleftとRightの使い分け方が決まります。
おわり
Inner Join を内部結合、Outer join を外部結合ともいいますが、日本語になっただけなのに難しく感じるのなんででしょうね。
