【SQL基礎】外部結合の基本構文

kaneko

外部結合とは

外部結合は、一方のテーブルを基準として、もう一方のテーブルから結合条件に一致するレコードを結合する方法です。

基準となるテーブルのレコードはすべて取得されます。
もう一方のテーブルからは条件に一致するレコードのみ取得されます。

以下はイメージ図です。（左のテーブルが基準）
※図中の”null”というのは、値が入っていない状態のことです。

もう少し具体的に示したのが以下の図です。

そもそも「結合」というもの自体よくわからないという方はこちらの記事で概要をご確認ください。

外部結合の種類

外部結合には、
・左外部結合
・右外部結合
・完全外部結合
の三種類があります。

詳細は後述します。

本記事では例として、以下の2つのテーブルを使用します。

・teachersテーブル
ID、名前、年齢、教科ID、住所IDの5つのカラムで構成されています。

・prefecturesテーブル
ID、都道府県名の2つのカラムで構成されています。

1.左外部結合(LEFT OUTER JOIN)

左外部結合はFROM句で指定したテーブルを基準に結合を行います。
基準となるテーブルに存在するレコードをすべて取得します。
結合条件に一致するレコードがもう一方のテーブルに存在しない場合はnullになります。
書式は以下の通りです。

SELECT カラム名
FROM テーブル名
LEFT (OUTER) JOIN テーブル名
ON 結合条件;
※OUTERは省略できます。

例1.teachersテーブルにprefecturesテーブルを結合する。

teachersテーブルを基準に左外部結合を行います。
取得するカラムはteachersテーブルのid,nameとprefecturesテーブルのnameとします。……A
prefecturesテーブルのnameカラムにはaddressという別名を付けます。……A
FROM句では基準となるteachersテーブルを指定して、”T”という別名を付けます。……B
JOIN句ではprefecturesテーブルを指定して、”P”という別名を付けます。……C
ON句では、teachersテーブルのaddress_idの値とprefecturesテーブルのidの値が一致するという結合条件を記述します。……D
※テーブルの別名については内部結合についての記事で詳しく扱っています。

SELECT T.id, T.name, P.name AS address  // A
FROM teachers T                         // B
LEFT JOIN prefectures P                 // C
ON T.address_id = P.id;                 // D

2.右外部結合(RIGHT OUTER JOIN)

右外部結合はJOIN句で指定したテーブルを基準に結合を行います。
基準となるテーブルに存在するレコードをすべて取得します。
結合条件に一致するレコードがもう一方のテーブルに存在しない場合はnullになります。
書式は以下の通りです。左外部結合の”LEFT”を”RIGHT”に変えるだけです。

SELECT カラム名
FROM テーブル名
RIGHT (OUTER) JOIN テーブル名
ON 結合条件;
※OUTERは省略できます。

例2-1.teachersテーブルにprefecturesテーブルを結合する。

例1と基本的には同様ですが、JOIN句はRIGHT JOINを使っています。
したがって、基準となるテーブルはprefecturesテーブルとなり、例1とは異なる結果が得られます。

SELECT T.id, T.name, P.name AS address
FROM teachers T
RIGHT JOIN prefectures P
ON T.address_id = P.id;

例2-2.prefecturesテーブルにteachersテーブルを結合する。

例2-1のFROM句とJOIN句のテーブルを入れ替えた例です。
FROM句にprefecturesテーブルを指定して、JOIN句でteachersテーブルを指定しています。
基準となるテーブルがteachersテーブルになるため、例1と同様の結果が得られます。

SELECT T.id, T.name, P.name AS address
FROM prefectures P
RIGHT JOIN teachers T
ON T.address_id = P.id;

3.完全外部結合(FULL OUTER JOIN)

完全外部結合はFROM句、JOIN句で指定したテーブルを基準に結合を行います。
両方のテーブルに存在するレコードをすべて取得します。
結合条件に一致するレコードがもう一方のテーブルに存在しない場合はnullになります。

完全外部結合はMySQLではサポートされていない結合方法です。
参考として、完全外部結合をしたのと同じ出力を得られるSQLを紹介しておきます。
“UNION”という句を使って左外部結合の結果と右外部結合の結果をひとまとめに表示しています。

SELECT T.id, T.name, P.name AS address
FROM teachers T
LEFT JOIN prefectures P
ON T.address_id = P.id
UNION
SELECT T.id, T.name, P.name AS address
FROM teachers T
RIGHT JOIN prefectures P
ON T.address_id = P.id;

学習のポイント

・左（右）外部結合は、一方のテーブルを基準とするときに使用する。
・完全外部結合はMySQLではサポートされていない。

練習問題

問題として使用するデータは「SQL練習問題」のものを使用しています。
以下からダウンロード可能です。
また、ダウンロードしたら以下のようにしてworldcup2014データベースを作成し、インポートしてください。
※worldcup2014.zip解凍してできるworldcup2014.sqlファイルをCドライブ直下に配置した場合。

worldcup2014.zip

[sql] CREATE DATABASE worldcup2014;

use worldcup2014

source C:\worldcup2014.sql
[/sql]

問、ブラジルの選手について、誰がどの時間帯にゴールを決めたのか調べるためのデータを取得してください。
その際、外部結合を使用してください。
参考として、playersテーブルとgoalsテーブルを以下に示しておきます。（一部省略）
※ブラジルの選手は、playersテーブルのcountry_idの値が1です。