MySQLで3つ以上のテーブルを完全外部結合する方法

はじめに

MySQL(8.0)では完全外部結合(FULL OUTER JOIN)が使えません。そこでJOIN句とUNION句を使って完全外部結合の結果を返します。他の記事だと2テーブルに対しては方法がすでに紹介されているので本記事では3テーブル以上の場合はどうするかを紹介します。

2テーブルの場合

数学のテストを受けた学生と点数のテーブル(math)と英語のテストを受けた学生と点数のテーブル(english)を結合します。どちらかのテストのみ受けた人も表示したい場合はstudentカラムをキーとした完全外部結合を行います。

2テーブルの完全外部結合は以下の考え方で行います。
1. 2つのテーブルに左外部結合(LEFT OUTER JOIN)
2. 2つのテーブルの右外部結合(RIGHT OUTER JOIN)
3. 2回結合した結果に対して和集合をとる(UNION)

よってmathテーブルとenglishテーブルは次のSQLで結合できます。

SELECT
    math.student,
    math.math_score,
    english.english_score
FROM
    math
LEFT OUTER JOIN
    english ON math.student = english.student
UNION
SELECT
    english.student,
    math.math_score,
    english.english_score
FROM
    math
RIGHT OUTER JOIN 
    english ON math.student = english.student

3テーブルの場合

今度は先ほどのテーブルに国語(japanese)も加えて完全外部結合をします。

3テーブルの完全外部結合は以下の考え方で行います。
1. With句で2テーブルの完全外部結合の結果を保持
2. 保持した結果と3つ目のテーブルを完全外部結合

3テーブルの結合は次のようになります。

WITH temp AS(
    SELECT 
        math.student,
        math.math_score,
        english.english_score
    FROM
        math
    LEFT OUTER JOIN
        english ON math.student = english.student
    UNION
    SELECT 
        english.student,
        math.math_score,
        english.english_score
    FROM
        math
    RIGHT OUTER JOIN 
        english ON math.student = english.student
)

SELECT
    temp.student,
    temp.math_score,
    temp.english_score,
    japanese.japanese_score
FROM
    temp
LEFT OUTER JOIN 
    japanese ON temp.student = japanese.student
UNION
SELECT 
    japanese.student,
    temp.math_score,
    temp.english_score,
    japanese.japanese_score
FROM
    temp
RIGHT OUTER JOIN
    japanese ON temp.student = japanese.student

4テーブル以上の場合

3テーブルの考え方と同様に結合した結果をWith句で保持して2テーブルの結合を繰り返します。