はじめに
私は業務で Ruby on Rails を使用しています。
Rails の Active Record がとても優秀なのは周知の事実。
そのため発行される SQL スクリプトを意識しないと、システムのボトルネックが次々と生み出されるのもこれまた周知の事実。
ということで、SQL をガシガシ勉強しています。
今回は外部結合に関して、書いていきたいと思います。
TL;DR
INNER JOIN
SELECT * FROM GroupA INNER JOIN GroupB ON GroupA.id = GroupB.id;
LEFT JOIN
SELECT * LEFT JOIN GroupB ON GroupA.id = GroupB.id;
RIGHT JOIN
SELECT * FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id;
LEFT JOIN EXCLUDING INNER JOIN
SELECT * FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupB.id IS NULL;
RIGHT JOIN EXCLUDING INNER JOIN
SELECT * FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupA.id IS NULL;
FULL OUTER JOIN
SELECT * FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id
UNION
SELECT * FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id;
FULL OUTER JOIN EXCLUDING INNER JOIN
SELECT * FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupB.id IS NULL
UNION
SELECT * FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupA.id IS NULL;
そもそものお話
この章では、
そもそも SQL のような宣言型言語では手続き型言語を扱っている時とは違う思考をする必要がある
ということを述べていきます。
宣言型と手続き型の違いに関して stack overflow の記事で良いものがありました。
Declarative programming is where you say what you want without having to say how to do it.
With procedural programming, you have to specify exact steps to get the result.For example, SQL is more declarative than procedural, because the queries don't specify steps to produce the result.
【引用: What is the difference between declarative and procedural programming paradigms?】
これを日本語に訳すと以下のようなニュアンスだと思います。
宣言型プログラミングはどのように実現するかを言うのではなく、欲しいものが何であるかを記述する。
手続き型プログラミングでは、結果を得るために正確なステップを明記する必要がある。例えば SQL は手続き的というよりも宣言的である。
それはクエリーは結果を生み出すためにステップを明記しないからである。
つまり、宣言型言語の入力はその名の通り欲しいものが何かを宣言するだけで良いということです。
入力の仕方が違うため、宣言型言語を扱う時には手続き型言語とは異なる思考の方法に切り替える必要があります。
このことは SQL のような宣言型言語を最初に学んだわけでも主戦場としているわけでもない私にとって目から鱗でした。
どのような思考の違いがあるのか、それは以下の画像がよく解説してくれています。
【引用: 手続き型から宣言型・集合指向に頭を切り替えるための7箇条】
四角を描くな、円を描け
ということです。
結合と集合
前章で SQL の思考に切り替わったかと思うので、ここからは本題の結合について述べていきます。
まず、結合は過程でありその成果物が集合であると私は認識しています。
そのため SQL スクリプトによる結合の方法を知っていても、目的とする集合がどのような集合であるかを捉えることができなければ結合の使いようがありません。
あるいは、知っている結合を総当たりで試すという非効率的なことをしなければなりません。
従って、結合操作とそれによって得られる集合を対応づけて覚える必要があります。
その時に具体的なサンプルデータで覚えるよりも絵で考える方が覚えやすいと個人的に思っています。
ここで、良い画像を見つけたのでドーンと載せちゃいます。
以降の章からはこれらの集合を得るための操作や考え方について述べていきます。
【引用: Ace the Coding Interview: SQL— Inner Join】
結合操作
今回使用するのは MySQL です。
また、バージョンは 8.0.34 を使用します。
$ mysql --version
mysql Ver 8.0.34 for macos13.3 on arm64 (Homebrew)
$
この章では以下のようなサンプルデータを用いてそれぞれの結合操作を行います。
CREATE TABLE GroupA (
id INTEGER NOT NULL,
name VARCHAR(8) NOT NULL
);
INSERT INTO GroupA VALUES
(1, '斉藤'),
(2, '島田'),
(3, '須藤');
CREATE TABLE GroupB (
id INTEGER NOT NULL,
name VARCHAR(8) NOT NULL
);
INSERT INTO GroupB VALUES
(1, '斉藤'),
(2, '島田'),
(4, '瀬古');
INNER JOIN
以下スクリプトは GroupA に対して GroupB を INNER JOIN した操作です。
SELECT GroupA.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA INNER JOIN GroupB ON GroupA.id = GroupB.id;
INNER JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 1 | 斉藤 | 斉藤 |
| 2 | 島田 | 島田 |
+----+--------+--------+
この結合操作は以下のようなイメージです。
なお、GroupB に対して GroupA を INNER JOIN してもその性質上同様の結果が得られます。
SELECT GroupB.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupB INNER JOIN GroupA ON GroupA.id = GroupB.id;
INNER JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 1 | 斉藤 | 斉藤 |
| 2 | 島田 | 島田 |
+----+--------+--------+
LEFT JOIN / RIGHT JOIN
以下スクリプトは GroupA に対して GroupB を LEFT JOIN した操作です。
SELECT GroupA.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id;
LEFT JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 1 | 斉藤 | 斉藤 |
| 2 | 島田 | 島田 |
| 3 | 須藤 | NULL |
+----+--------+--------+
この結合操作は以下のようなイメージです。
LEFT JOIN では基準は FROM 句の集合となります。(今回のケースでは GroupA)
次に、GroupA に対して GroupB を RIGHT JOIN します。
SELECT GroupB.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id;
RIGHT JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 1 | 斉藤 | 斉藤 |
| 2 | 島田 | 島田 |
| 4 | NULL | 瀬古 |
+----+--------+--------+
RIGHT JOIN では基準は RIGHT JOIN 句の集合です。(今回のケースでは GroupB)
以上のように、LEFT JOIN と RIGHT JOIN は操作します。
LEFT JOIN と RIGHT JOIN の違いは左右どちらの集合を基準にするかという点にあるので、等価的に操作することができます。
INNER JOIN を除いた LEFT JOIN / RIGHT JOIN
INNER JOIN を除いた LEFT JOIN / RIGHT JOIN は 前節の LEFT JOIN / RIGHT JOIN の操作に加えて、 WHERE 句によって基準ではない方のテーブルの id が NULL であるものを絞り込むことで得ることができます。
以下スクリプトは GroupA に対して GroupB を LEFT JOIN して、INNER JOIN を除いた操作です。
SELECT GroupA.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupB.id IS NULL;
INNER JOIN を除いた LEFT JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 3 | 須藤 | NULL |
+----+--------+--------+
この結合操作は以下のようなイメージです。
次に、GroupA に対して GroupB を RIGHT JOIN し、INNER JOIN を除いた操作を示します。
SELECT GroupB.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupA.id IS NULL;
INNER JOIN を除いた RIGHT JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 4 | NULL | 瀬古 |
+----+--------+--------+
FULL OUTER JOIN
MySQL では現在 FULL OUTER JOIN をサポートしていないため UNION 句を使用します。
UNION 句については、公式では以下のように説明されています。
UNION は、複数の SELECT ステートメントの結果を単一の結果セットに結合します。
(略)
デフォルトでは、重複行は UNION の結果から削除されます。
【引用: MySQL 8.0 リファレンスマニュアル UNION 句】
この UNION 句の性質によって、FULL OUTER JOIN を実現することができます。
具体的には、LEFT JOIN の SELECT 文と RIGHT JOIN の SELECT 文を UNION によって結合します。
INNER JOIN の部分が重複しますが、説明の通り重複行は UNION の結果から削除されるため心配事ではありません。
以下スクリプトは擬似的に GroupA と GroupB を FULL OUTER JOIN した操作です。
SELECT GroupA.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id
UNION
SELECT GroupB.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id;
FULL OUTER JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 1 | 斉藤 | 斉藤 |
| 2 | 島田 | 島田 |
| 3 | 須藤 | NULL |
| 4 | NULL | 瀬古 |
+----+--------+--------+
以下は FULL OUTER JOIN の結合イメージです。
(※ UNION を使用した上記のスクリプトの結合操作イメージではありません)
INNER JOIN を除いた FULL OUTER JOIN
INNER JOIN を除いた FULL OUTER JOIN を MySQL を使用する場合、前々節「INNER JOIN を除いた LEFT JOIN / RIGHT JOIN」を利用することで実現することができます。
具体的には、INNER JOIN を除いた LEFT JOIN と INNER JOIN を除いた RIGHT JOIN を UNION によって結合することで、INNER JOIN を除いた FULL OUTER JOIN を実現できます。
つまり、発想は前節と全く同じです。
以下スクリプトは擬似的に GroupA と GroupB を FULL OUTER JOIN して、INNER JOIN を除いた操作です。
SELECT GroupA.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA LEFT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupB.id IS NULL
UNION
SELECT GroupB.id, GroupA.name AS A_name, GroupB.name AS B_name
FROM GroupA RIGHT JOIN GroupB ON GroupA.id = GroupB.id
WHERE GroupA.id IS NULL;
FULL OUTER JOIN 操作結果
+----+--------+--------+
| id | A_name | B_name |
+----+--------+--------+
| 3 | 須藤 | NULL |
| 4 | NULL | 瀬古 |
+----+--------+--------+
おわりに
今回一気に七種類の結合操作を見ました。
これら全てを覚えるのは大変ですよね。
しかし、実際は INNER JOIN, LEFT JOIN, FULL OUTER JOIN の三種類さえ抑えれば、あとは派生的に覚えることができると思います。
今回 SQL の内部結合・外部結合を抑えることができたので、次回は Rails の ActiveRecord とこれら結合を絡めた記事を書く予定です。