PR

【SQL】OUTER JOIN(外部結合)を3分でわかりやすく解説

Database

OUTER JOIN(外部結合)は、テーブルとテーブルを結合するためのSQL文です。

2つのテーブルを結合し、指定したキーで一致するレコードを結合するだけでなく、一致しないレコードも結果セットに含めます。一致しないレコードの欠けた値は NULL で埋められます。

OUTER JOIN わかりやすく
/* テーブルA
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |
|  4 | 中村 | A     |       90 |   65 |      85 |
|  5 | 小林 | B     |       70 |   90 |      65 |
|  6 | 山口 | B     |       90 |   85 |      85 |
|  7 | 田中 | B     |       70 |   90 |      65 |
|  8 | 伊藤 | B     |       70 |   90 |      65 |
+----+------+-------+----------+------+---------+
*/

/* テーブルB
+----+------+-------+---------+---------------+
| ID | Name | Class | Science | SocialStudies |
+----+------+-------+---------+---------------+
|  1 | 佐藤 | A     |      90 |            75 |
|  2 | 鈴木 | A     |      90 |            70 |
|  3 | 高橋 | A     |      85 |            70 |
|  4 | 中村 | A     |      40 |             0 |
+----+------+-------+---------+---------------+
*/

SELECT
  A.ID, A.Name, A.Class, A.Japanese, A.Math, A.English, B.Science, B.SocialStudies
FROM
  PointList AS A
LEFT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

/* 結果
+----+------+-------+----------+------+---------+---------+---------------+
| ID | Name | Class | Japanese | Math | English | Science | SocialStudies |
+----+------+-------+----------+------+---------+---------+---------------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |      90 |            75 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |      90 |            70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |      85 |            70 |
|  4 | 中村 | A     |       90 |   65 |      85 |      40 |             0 |
|  5 | 小林 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  6 | 山口 | B     |       90 |   85 |      85 |    NULL |          NULL |
|  7 | 田中 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  8 | 伊藤 | B     |       70 |   90 |      65 |    NULL |          NULL |
+----+------+-------+----------+------+---------+---------+---------------+
*/

テーブル結合には大きく分類すると内部結合と外部結合―。2種類の結合パターンが存在しますが、本ページでは外部結合(OUTER JOIN)について解説しています。

内部結合/外部結合
  • 内部結合
    両方のテーブルに存在するキーを条件に結合
  • 外部結合
    1つのテーブルを基準として結合(指定したキーで一致するレコードを結合するだけでなく、一致しないレコードも結果セットに含める)

このページではSQL初心者向けにCASE文の利用方法をわかりやすく丁寧に図解付き・サンプルコード付きで解説します。

このページで学べる内容
  • 【前提】テーブル結合とは?
  • OUTER JOIN(外部結合)とは
    • OUTER JOIN の構文ルール
    • LEFT OUTER JOIN / RIGHT OUTER JOIN

プログラマーエンジニアを目指す方であれば知らないと恥ずかしい超・基本知識です。是非最後までご覧ください。

スポンサーリンク

OUTER JOIN:外部結合とは?

OUTER JOIN わかりやすく
図1:テーブル結合

OUTER JOIN(外部結合)はテーブルとテーブルを結合する際に用います。

1つのテーブルだけでは情報が不足しているような場合に、他のテーブルのデータの情報も併せて表示したい場合などに用います。

INNER JOIN わかりやすく

OUTER JOIN:構文ルール

SQL:OUTER JOIN
SELECT
  A.カラム名, B.カラム名
FROM
  テーブルA
[RIGHT/LEFT] OUTER JOIN
  テーブルB
ON
  A.結合キー = B.結合キー;
  • FROM句:OUTER JOINで結合対象となるテーブルを記述
  • ON句:結合キーを記述

結合キーは、テーブルとテーブルを結合する際の基準となるカラムのことです。

例えば以下2つのテーブルを結合する際に「役職ID」を基準にする場合、この「役職ID」が結合キーです。

INNER JOIN 内部結合

今回は実際にサンプルとなるテーブルを用意し、どのようにINNER JOIN(内部結合)の処理が行われるか?を確認していきましょう。

OUTER JOIN 外部結合
図2:サンプルテーブル

実際に自分で動かしてみたいという方のために、テーブルのCREATE文とレコード登録のためのINSERT文を掲載しておきます。

テーブル定義:参考(CREATE TABLE

-- テーブルA
CREATE TABLE PointList (
ID       INT(3)       NOT NULL PRIMARY KEY,
Name     VARCHAR(50)  NOT NULL,
Class    VARCHAR(1),
Japanese INT(3)       DEFAULT '0',
Math     INT(3)       DEFAULT '0', 
English  INT(3)       DEFAULT '0'
);

/*
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(3)      | NO   | PRI | NULL    |       |
| Name     | varchar(50) | NO   |     | NULL    |       |
| Class    | varchar(1)  | YES  |     | NULL    |       |
| Japanese | int(3)      | YES  |     | 0       |       |
| Math     | int(3)      | YES  |     | 0       |       |
| English  | int(3)      | YES  |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
*/

-- テーブルB
CREATE TABLE SubPointList (
ID             INT(3)       NOT NULL PRIMARY KEY,
Name           VARCHAR(50)  NOT NULL,
Class          VARCHAR(1),
Science        INT(3)       DEFAULT '0',
SocialStudies  INT(3)       DEFAULT '0'
);

/*
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| ID            | int(3)      | NO   | PRI | NULL    |       |
| Name          | varchar(50) | NO   |     | NULL    |       |
| Class         | varchar(1)  | YES  |     | NULL    |       |
| Science       | int(3)      | YES  |     | 0       |       |
| SocialStudies | int(3)      | YES  |     | 0       |       |
+---------------+-------------+------+-----+---------+-------+
*/

利用するダミーレコード:参考(INSERT文

INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (1,'佐藤','A',100,100,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (2,'鈴木','A',90,70,70);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (3,'高橋','A',85,70,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (4,'中村','A',90,65,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (5,'小林','B',70,90,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (6,'山口','B',90,85,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (7,'田中','B',70,90,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (8,'伊藤','B',70,90,65);

-- テーブルA確認
SELECT * FROM PointList;
/*
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |
|  4 | 中村 | A     |       90 |   65 |      85 |
|  5 | 小林 | B     |       70 |   90 |      65 |
|  6 | 山口 | B     |       90 |   85 |      85 |
|  7 | 田中 | B     |       70 |   90 |      65 |
|  8 | 伊藤 | B     |       70 |   90 |      65 |
+----+------+-------+----------+------+---------+
*/

INSERT INTO SubPointList (ID,Name,Class,Science,SocialStudies) VALUES (1,'佐藤','A',90,75);
INSERT INTO SubPointList (ID,Name,Class,Science,SocialStudies) VALUES (2,'鈴木','A',90,70);
INSERT INTO SubPointList (ID,Name,Class,Science,SocialStudies) VALUES (3,'高橋','A',85,70);
INSERT INTO SubPointList (ID,Name,Class,Science,SocialStudies) VALUES (4,'中村','A',40,0);

-- テーブルB確認
SELECT * FROM SubPointList;
+----+------+-------+---------+---------------+
| ID | Name | Class | Science | SocialStudies |
+----+------+-------+---------+---------------+
|  1 | 佐藤 | A     |      90 |            75 |
|  2 | 鈴木 | A     |      90 |            70 |
|  3 | 高橋 | A     |      85 |            70 |
|  4 | 中村 | A     |      40 |             0 |
+----+------+-------+---------+---------------+

OUTER JOIN:サンプルコード

2つのテーブルを "ID列" を結合キーとしてOUTER JOIN(外部結合)した結果がこちらです。

SELECT
  A.ID, A.Name, A.Class, A.Japanese, A.Math, A.English, B.Science, B.SocialStudies
FROM
  PointList AS A
LEFT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

/* 結果
+----+------+-------+----------+------+---------+---------+---------------+
| ID | Name | Class | Japanese | Math | English | Science | SocialStudies |
+----+------+-------+----------+------+---------+---------+---------------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |      90 |            75 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |      90 |            70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |      85 |            70 |
|  4 | 中村 | A     |       90 |   65 |      85 |      40 |             0 |
|  5 | 小林 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  6 | 山口 | B     |       90 |   85 |      85 |    NULL |          NULL |
|  7 | 田中 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  8 | 伊藤 | B     |       70 |   90 |      65 |    NULL |          NULL |
+----+------+-------+----------+------+---------+---------+---------------+
*/

外部結合(OUTER JOIN)のポイントは大きく2つ。

OUTER JOIN(外部結合)のポイント
  • 2つのテーブルを結合し、指定したキーで一致するレコードを結合するだけでなく、一致しないレコードも結果セットに含める
  • SELECT句では 「テーブル名.カラム名」 の形で記述

外部結合のポイント①:一致しないレコードも結果セットに含める

OUTER JOIN(外部結合)では、内部結合とは異なり指定したキーで一致しないレコードも結果に含めます。

テーブルAを基準とする場合、テーブルAに存在するレコードをすべて結果に取得しつつ、もしテーブルBにも一致するキーがあればそのレコードを補足して取得、なければNULLで結果に含める動きになります。

INNER JOIN はON句指定した結合キーが両方のテーブルに存在する場合にのみ結合されます。

INNER JOIN 内部結合

外部結合のポイント②:SELECT句は「テーブル名.カラム名」の形で記述

先ほどのSQL文のSELECT句をもう一度確認してみましょう。

SELECT
  A.ID, A.Name, A.Class, A.Japanese, A.Math, A.English, B.Science, B.SocialStudies
FROM
  PointList AS A
LEFT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

これは、両方のテーブルに同じカラム名(例 ID,Name,Class)が存在しているため、その記述の曖昧さを避けるための措置です。(=どちらのテーブルのカラム名を表示すればよいか?をシステムが判断することができなくなってしまう。

-- ID,Name,Classは結合対象の両方のテーブルに含まれるためエラーになる
SELECT
  ID,Name,Class,Japanese,Science
FROM
  PointList
LEFT OUTER JOIN
  SubpointList
ON
  PointList.ID = SubpointList.ID;

-- ERROR 1052 (23000): Column 'ID' in field list is ambiguous

したがって、片方のテーブルのみに存在するレコードを利用する場合は、厳密に「テーブル名.カラム名」の形で記述する必要はありません。

ただし、JOINを用いる場合には、SQLを読みやすくするために基本的に「テーブル名.カラム名」の形で記述することが一般的です。

*を利用するパターン

SELECT句にワイルドカード「*」を利用すると以下のように両方のテーブルに存在するすべてのカラムが取得対象となります。

SELECT
  *
FROM
  PointList AS A
LEFT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

/* 結果→全カラムがそのまま表示される
+----+------+-------+----------+------+---------+------+------+-------+---------+---------------+
| ID | Name | Class | Japanese | Math | English | ID   | Name | Class | Science | SocialStudies |
+----+------+-------+----------+------+---------+------+------+-------+---------+---------------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |    1 | 佐藤 | A     |      90 |            75 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |    2 | 鈴木 | A     |      90 |            70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |    3 | 高橋 | A     |      85 |            70 |
|  4 | 中村 | A     |       90 |   65 |      85 |    4 | 中村 | A     |      40 |             0 |
|  5 | 小林 | B     |       70 |   90 |      65 | NULL | NULL | NULL  |    NULL |          NULL |
|  6 | 山口 | B     |       90 |   85 |      85 | NULL | NULL | NULL  |    NULL |          NULL |
|  7 | 田中 | B     |       70 |   90 |      65 | NULL | NULL | NULL  |    NULL |          NULL |
|  8 | 伊藤 | B     |       70 |   90 |      65 | NULL | NULL | NULL  |    NULL |          NULL |
+----+------+-------+----------+------+---------+------+------+-------+---------+---------------+
*/

LEFT OUTER JOIN / RIGHT OUTER JOIN

さてここまでの外部結合のサンプルコードはすべてLEFT OUTER JOINを利用しておりましたが、RIGHT OUTER JOINを利用するとどうなるでしょうか?

以下がRIGHT OUTER JOINを実行した結果です。

-- RIGHT OUTER JOIN
SELECT
  A.ID, A.Name, A.Class, A.Japanese, A.Math, A.English, B.Science, B.SocialStudies
FROM
  PointList AS A
RIGHT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

/* 結果
+------+------+-------+----------+------+---------+---------+---------------+
| ID   | Name | Class | Japanese | Math | English | Science | SocialStudies |
+------+------+-------+----------+------+---------+---------+---------------+
|    1 | 佐藤 | A     |      100 |  100 |      85 |      90 |            75 |
|    2 | 鈴木 | A     |       90 |   70 |      70 |      90 |            70 |
|    3 | 高橋 | A     |       85 |   70 |      65 |      85 |            70 |
|    4 | 中村 | A     |       90 |   65 |      85 |      40 |             0 |
+------+------+-------+----------+------+---------+---------+---------------+
*/


--参考 LEFT OUTER JOIN
SELECT
  A.ID, A.Name, A.Class, A.Japanese, A.Math, A.English, B.Science, B.SocialStudies
FROM
  PointList AS A
LEFT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

/* 結果
+----+------+-------+----------+------+---------+---------+---------------+
| ID | Name | Class | Japanese | Math | English | Science | SocialStudies |
+----+------+-------+----------+------+---------+---------+---------------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |      90 |            75 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |      90 |            70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |      85 |            70 |
|  4 | 中村 | A     |       90 |   65 |      85 |      40 |             0 |
|  5 | 小林 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  6 | 山口 | B     |       90 |   85 |      85 |    NULL |          NULL |
|  7 | 田中 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  8 | 伊藤 | B     |       70 |   90 |      65 |    NULL |          NULL |
+----+------+-------+----------+------+---------+---------+---------------+
*/

ご覧のように結果が異なります。

左外部結合 (LEFT OUTER JOIN) と右外部結合 (RIGHT OUTER JOIN) の違いは、どちらのテーブルに一致しないレコードを含めるのか?という点です。

左外部結合 vs 右外部結合
  • 左テーブル (最初に指定されたテーブル) の全レコードが結果セットに含まれる
  • 右テーブルの全レコードが結果セットに含まれる

FROM句で指定したテーブルが左テーブルになります。

left outer join right outer join

基準とするテーブルをどちらにするか?という点だけが相違点です。

基本は LEFT OUTER JOIN を利用すればOKです。RIGHT OUTER JOIN を利用しないといけないシーンは存在しないので、可読性を高めることを目的としてLEFT OUTER JOINに統一して利用する人が多いようです。

OUTER JOIN(外部結合)のまとめ
  • テーブルとテーブルを結合させる際に利用する
  • 指定したキーで一致するレコードを結合するだけでなく、一致しないレコードも結果セットに含める。
  • SELECT句では 「テーブル名.カラム名」 の形で記述する
/* テーブルA
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |
|  4 | 中村 | A     |       90 |   65 |      85 |
|  5 | 小林 | B     |       70 |   90 |      65 |
|  6 | 山口 | B     |       90 |   85 |      85 |
|  7 | 田中 | B     |       70 |   90 |      65 |
|  8 | 伊藤 | B     |       70 |   90 |      65 |
+----+------+-------+----------+------+---------+
*/

/* テーブルB
+----+------+-------+---------+---------------+
| ID | Name | Class | Science | SocialStudies |
+----+------+-------+---------+---------------+
|  1 | 佐藤 | A     |      90 |            75 |
|  2 | 鈴木 | A     |      90 |            70 |
|  3 | 高橋 | A     |      85 |            70 |
|  4 | 中村 | A     |      40 |             0 |
+----+------+-------+---------+---------------+
*/

SELECT
  A.ID, A.Name, A.Class, A.Japanese, A.Math, A.English, B.Science, B.SocialStudies
FROM
  PointList AS A
LEFT OUTER JOIN
  SubPointList AS B
ON
  A.ID = B.ID;

/* 結果
+----+------+-------+----------+------+---------+---------+---------------+
| ID | Name | Class | Japanese | Math | English | Science | SocialStudies |
+----+------+-------+----------+------+---------+---------+---------------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |      90 |            75 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |      90 |            70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |      85 |            70 |
|  4 | 中村 | A     |       90 |   65 |      85 |      40 |             0 |
|  5 | 小林 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  6 | 山口 | B     |       90 |   85 |      85 |    NULL |          NULL |
|  7 | 田中 | B     |       70 |   90 |      65 |    NULL |          NULL |
|  8 | 伊藤 | B     |       70 |   90 |      65 |    NULL |          NULL |
+----+------+-------+----------+------+---------+---------+---------------+
*/

SQLを1から学習したい方は

SQLやデータベースの仕組みを1から学習したい方(学び直したい方)向けに、現役エンジニア達のスキルを結集して 完全無料 のSQL教材を作成しました。

SQLは決して難しい技術ではないので、エンジニアであれば「当たり前のように」扱えて当然かも・・・?

とはいえ、案外SQLをちゃんと使ったことがない人も多いはずです。この機会に是非一度ご覧になってみてください。

このWebサイトは現役のエンジニアが以下3点を目的として運営しています。

  1. 勉強:一度理解した内容を忘れないように。
    → アウトプットは「最強のインプット」である! 
  2. 備忘:忘れたとしても後から見返せるように。
    → 未来の自分への「お手紙」を書いています。 
  3. 共有:〇〇ってこうだったんだ!の感動をシェアできるように。
    → あなたの知識は誰かにとっての「価値ある情報」です。 

副業ブログの始め方はこちらから

スポンサーリンク
DatabaseIT-Skills
シェアする
ビズドットオンラインをフォローする
タイトルとURLをコピーしました