1週間で身に付くMySQL #6

Tweet

Pocket

こんばんわ、未経験からITエンジニア転職を目指すいずみです。

短期集中連載のMySQLシリーズです。前回は、”内部結合”および”交差結合”について、学びました。

今回は、内部結合と並んでよく用いられる“外部結合”について、アウトプットしたいと思います。

外部結合(Outer)とは

参考教材から引用すると、外部結合についてこのように書かれています。

外部結合は、それぞれのテーブルの指定した列の値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得します。

外部結合とは

イメージが難しいですが、、、この時点で分かったことは、内部結合では「設定したキーと合致したレコードを取得する」のに対して、外部結合では「キーと合致しないレコードも取得する」ということでしょうか。

外部結合の構文

前回学んだ“内部結合”の構文を振り返ってみましょう。

# ①casts ②faction_nameというテーブルがあったとする。
# ①と②を"faction"というカラムをキーとして、結合する。
SELECT * FROM casts INNER JOIN faction_name ON casts.faction = faction_name.faction;

テーブル① INNER JOIN テーブル② ON テーブル①カラム = テーブル②.カラムという書き方で「2つのテーブルを結合する」ことを表していますね。

外部結合も2つのテーブルを結合するので、上記と構文はそっくりです。

# 外部結合の構文①
SELECT テーブル名.カラム名, ... FROM テーブル名1
LEFT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

# 外部結合の構文②
SELECT テーブル名.カラム名, ... FROM テーブル名1
RIGHT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

構文が2つあるのは、外部結合が「キーと合致しないレコードも取得する」という特徴を有するからです。

要するに、もし「キーと合致しなかった」とき、どちらのテーブルの値を取得するかによって、構文を使い分けるということです。

• LEFT OUTER JOIN – テーブル①だけ取得する
• RIGHT OUTER JOIN – テーブル②だけ取得する

外部結合の下準備

外部結合を行うために、新たなテーブルを追加します。

“鎌倉殿の放送回に派閥のキャストが何人出てきたかを時系列で管理するテーブル”にしようと思います。

<カラムの定義>

• DATE – 鎌倉殿の放送回
• faction – 役柄の派閥コード
• times – DATEにおける派閥の出演回数

# performanceテーブル
CREATE TABLE performance(
	date	DATE	NOT NULL,
	faction	char(4)	NOT NULL,
	times	INT		NOT NULL
);

INSERT INTO performance VALUES ('2022/1/13','kama',5);
INSERT INTO performance VALUES ('2022/1/13','hojo',11);
INSERT INTO performance VALUES ('2022/1/13','band',4);
INSERT INTO performance VALUES ('2022/1/20','kama',9);
INSERT INTO performance VALUES ('2022/1/20','hojo',7);
INSERT INTO performance VALUES ('2022/1/27','hojo',7);
INSERT INTO performance VALUES ('2022/1/27','band',9);
INSERT INTO performance VALUES ('2022/2/4','kama',12);
INSERT INTO performance VALUES ('2022/2/11','kama',5);
INSERT INTO performance VALUES ('2022/2/11','hojo',9);
INSERT INTO performance VALUES ('2022/1/13','tair',4);
INSERT INTO performance VALUES ('2022/1/20','tair',15);
INSERT INTO performance VALUES ('2022/1/27','tair',1);

Performanceテーブルは、”1/13放送回の鎌倉家は5回登場”といった形で読み取ってください。

外部結合の準備ができました。

外部結合をやってみる

次の2つのテーブルを使って、外部結合してみます。

①鎌倉殿のキャストテーブル(casts)

②派閥の登場回数管理テーブル(performance)

それぞれのテーブルに共通する“faction(派閥)カラム”をキーとして、LEFTとRIGHTの結合をおこなってみます。

LEFT OUTER JOINによる外部結合

LEFTというのは、もし「キーと合致しなかった」とき、“OUTER JOINの左側”に記述されたテーブルの値を取得するという意味です。

つまり、合致しなかったときは”performance”の値を取得するということですね。

SELECT date,role,name,performance.faction,times FROM performance LEFT OUTER JOIN casts ON performance.faction = casts.faction;

RIGHT OUTER JOINによる外部結合

RIGHTというのは、もし「キーと合致しなかった」とき、“OUTER JOINの右側”に記述されたテーブルの値を取得するという意味です。

つまり、合致しなかったときは”casts”の値を取得するということですね。

SELECT date,role,name,performance.faction,times FROM performance RIGHT OUTER JOIN casts ON performance.faction = casts.faction;

LEFTとRIGHTで実行結果が異なる件

同じテーブル同士を同じ条件で結合したのですが、以下の点で結果が異なりました。

• LEFTのとき、一部レコードにNULLが格納された。

処理の流れを追って、違いが現れた原因を説明してみます。

<LEFT OUTER JOINの結果>

SELECT date,role,name,performance.faction,times FROM performance LEFT OUTER JOIN casts ON performance.faction = casts.faction;

LEFTなので、“performanceテーブル”にあるデータは全て表示されます。

しかし、performanceテーブルのfactionカラムに、”tair”という平家を表す派閥コードがありますが、castsテーブルのfactionカラムには存在しません。

そのため、castsテーブルに該当する箇所は“NULL”と表示されます。

<RIGHT OUTER JOINの結果>

SELECT date,role,name,performance.faction,times FROM performance RIGHT OUTER JOIN casts ON performance.faction = casts.faction;

同様に見ていくと、RIGHTなので、“castsテーブル”にあるデータは全て表示されます。

castsテーブルに登場する外部キー(faction)の値(kama, band, hojo)は、全てperformanceテーブルの外部キーに含まれているので、NULL無く表示されています。

まとめ

いろいろやってきましたが、それぞれの結合の違いをまとめると、、

• 外部結合は、共通部分がないレコードも結合し、該当するデータがないカラムには、NULLを入れる。
• 内部結合は、共通部分があるものしか結合しない。

ということがわかりました！

今回までMySQLについて短期集中でやってきましたが、#6で一旦終わりとします。

発展編(データの変更・削除・正規化）として、もう少しカリキュラムが用意されているので、時間を見つけてやっていきたいと思います。