SQL入門第5回テーブルの結合

前回はSELECT文を使ってテーブルからデータを取得する方法について説明しました。

今回のテーマは「テーブルの結合」です。

SELECT文でデータを取得するときに、複数のテーブルからデータを取得したい場合がよくあります。そういう場合は、取得したいテーブルを”結合して取得する“という方法を用いることになります。

1 集合演算子(UNION,INTERSECT,EXCEPT)
2 結合演算子(LEFT, RIGHT, INNER, FULL, CROSS)
3 練習問題
- 3.1 問題
- 3.2 答え

集合演算子(UNION,INTERSECT,EXCEPT)

テーブルの結合は大きく「集合演算子」を用いたものと「結合演算子」を用いたものに分けられます。

まずは、集合演算子についてです。

UNION

UNIONは2つ以上のSELECTをくっつけて取得する際に使用します。くっつけるSELECT結果は全ての列の型と列の数が一致している必要があります。

以下のような2つのテーブルがあったとします。

吹奏楽部、あるいは陸上部に所属している学生の学籍番号と名前を取得したい場合は、以下のように記述します。

SELECT
    学籍番号, 名前
FROM
    吹奏楽部表
UNION
SELECT
    学籍番号, 名前
FROM
    陸上部表

Cさんは吹奏楽部・陸上部の両方に所属していますが、UNIONすると重複は排除されて、各1行ずつが取得されます。

重複も含めて表示したい場合は「UNION ALL」を用います。

SELECT
    学籍番号, 名前
FROM
    吹奏楽部表
UNION ALL
SELECT
    学籍番号, 名前
FROM
    陸上部表

INTERSECT

INTERSECTは2つのSELECTの取得結果の両方に含まれるレコードを表示する演算子です。
※MySQLではサポート外です

吹奏楽部と陸上部の両方に所属する生徒を取得したい場合に使用できます。

SELECT
    学籍番号, 名前
FROM
    吹奏楽部表
INTERSECT
SELECT
    学籍番号, 名前
FROM
    陸上部

EXCEPT

EXCEPTは2つのSELECTの取得結果の引き算のような使い方をします。
※MySQLはサポート外です
※ORACLEの場合はMINUSを使います

例えば、「吹奏楽部に所属していて陸上部に所属していない」生徒を取得したい場合に使用します。

SELECT
    学籍番号, 名前
FROM
    吹奏楽部表
EXCEPT
SELECT
    学籍番号, 名前
FROM
    陸上部

結合演算子(LEFT, RIGHT, INNER, FULL, CROSS)

テーブルの結合方法は大きく外部結合、内部結合、完全外部結合、交差結合の4種類に分かれます。

外部結合(LEFT JOIN, RIGHT JOIN)

外部結合は、言葉で説明するのは難しいのでまずは例を見てもらいます。

以下のような生徒マスタと部活マスタがあったとします。

この2つのテーブルから、それぞれの生徒と生徒が所属している部活名を外部結合を使って取得するには、以下のように記述します。

SELECT
    *
FROM
    生徒マスタ
LEFT JOIN
    部活マスタ
    ON 生徒マスタ.部活コード = 部活マスタ.部活コード

このようにしてLEFT JOINを使って生徒マスタに部活マスタをくっつけて表示させることが出来ます。

ON句ではテーブルを結合させるときにキーとなる列をします。

そして、LEFT JOINの場合は、左側に指定したテーブルに右側に指定したテーブルをくっつけます。

もし左側にあって、右側に結合するものがない場合はNULLで取得されます。

逆に、右側に指定したテーブルに左側に指定したテーブルをくっつけたい場合はRIGHT JOINを使います。

SELECT
    *
FROM
    生徒マスタ
RIGHT JOIN
    部活マスタ
    ON 生徒マスタ.部活コード = 部活マスタ.部活コード

LEFT JOINとRIGHT JOINは、ON句で指定している左右どちらを基準にするかを決めているだけなので、「=」の左右を逆にしてあげれば、LEFT JOINだけでも結果がRIGHT JOINになります。

SELECT
    *
FROM
    生徒マスタ
LEFT JOIN
    部活マスタ
    ON 部活マスタ.部活コード = 生徒マスタ.部活コード

つまり、外部結合はLEFT JOINだけで全て表現できるということになります。RIGHT JOINを使うと混乱の原因になるので使わないことを強く推奨します！

内部結合(INNER JOIN)

内部結合は、2つのテーブルを結合するときにキーとする項目が両方に存在するレコードだけを取得します。

以下のような生徒マスタと部活マスタがあったとします。

この2つのテーブルから生徒と所属している部活の部活名を取得するために結合を用いますが、内部結合(INNER JOIN)を使うと、

部活に所属していない生徒と所属部員がいない部活は除外して表示されます

SELECT
    *
FROM
    生徒マスタ
INNER JOIN
    部活マスタ
    ON 生徒マスタ.部活コード = 部活マスタ.部活コード

部活に入っていないEさんは表示されないし、部員がいない手芸部も表示されません。

このように、両方のテーブルに存在するレコードだけ取得したい場合に、INNER JOINを用います。

完全外部結合(FULL JOIN)

FULL JOINはLEFT JOINとRIGHT JOINの合体バージョンみたいなもので、右側のテーブルにしか存在しないもの、あるいは左側のテーブルにしか存在しないものも表示します。もちろん両方に存在するレコードも表示されます。

めったに使用しないので、具体例はここでは割愛します。

交差結合(CROSS JOIN)

CROSS JOINは他の結合とは少し考え方が異なりますが時々使用します。
一言でいえば「総当たりの表」のようなものを作成する際に使用します。

まずは、例を見てみましょう。

上記のように、生徒マスタと部活マスタがあったとします。
担任教師は、誰がどの部活に所属しているかを把握しません。

このとき、アンケートを取るための表を作成するために、CROSS JOINが使えます。

SELECT
    名前, 部活名, '' AS ○ or ✖
FROM
    生徒マスタ
CROSS JOIN
    部活マスタ

このように、生徒と部活の総当たり表が取得できます。

アンケートの結果をここに記入すれば、全ての生徒の部活への加入状況が分かりますよね。このように、パターンを洗い出したい場合などによく使用します。

練習問題

問題

以下のようなテーブルがある。

Q1. Cさんが保持している権限の権限名を表示せよ

Q2. 全社員の権限の保持状況を確認したい。社員が保持している権限情報を表示せよ。表示する列は、社員の名前と、権限名。ただし、権限を保持していない社員の権限名にはNULLが表示されるようにせよ。

Q3.勤続年数が5年以上の社員だけが保持している権限を表示せよ。

答え

A1.

SELECT
    権限名
FROM
    社員マスタ
INNER JOIN
    社員権限
    ON 社員マスタ.社員番号 = 社員権限.社員番号
INNER JOIN
    権限マスタ
    ON 社員権限.保持権限 = 権限マスタ.権限ID
WHERE
    社員マスタ.名前 = 'Cさん'

A2.

SELECT
    社員マスタ.名前, 権限マスタ.権限名
FROM
    社員マスタ
LEFT JOIN
    社員権限
    ON 社員マスタ.社員番号 = 社員権限.社員番号
LEFT JOIN
    権限マスタ
    ON 社員権限.保持権限 = 権限マスタ.権限ID

A3.

SELECT
    権限名
FROM
    社員マスタ
INNER JOIN
    社員権限
    ON 社員マスタ.社員番号 = 社員権限.社員番号
INNER JOIN
    権限マスタ
    ON 社員権限.保持権限 = 権限マスタ.権限ID
WHERE
    社員マスタ.勤続年数 >= 5
GROUP BY
    権限名