SQL FULL OUTER JOIN -- 両テーブルのすべての行を返す

FULL OUTER JOINとは？

FULL OUTER JOINはLEFT JOINとRIGHT JOINの動作を組み合わせます。両テーブルのすべての行を返します。マッチする行は通常通り結合され、いずれかの側にマッチがない行は欠落カラムがNULLで埋められます。

構文

SELECT a.id, a.name, b.id, b.project
FROM employees AS a
FULL OUTER JOIN projects AS b
  ON a.id = b.employee_id;

結果の構造

結果セットには3つのカテゴリの行が含まれます：

カテゴリ	左カラム	右カラム
マッチ	実データ	実データ
左のみ	実データ	NULL
右のみ	NULL	実データ

ベン図の解釈

FULL OUTER JOINは両方の円全体をハイライトします。左のみの領域、共通部分、右のみの領域すべてです。何も除外されません。

MySQLの回避策

MySQLはFULL OUTER JOIN構文をサポートしていません。LEFT JOINとRIGHT JOINのUNIONでエミュレートする必要があります：

SELECT a.*, b.*
FROM table_a AS a
LEFT JOIN table_b AS b ON a.id = b.id
UNION
SELECT a.*, b.*
FROM table_a AS a
RIGHT JOIN table_b AS b ON a.id = b.id;

UNION（UNION ALLではなく）を使用すると、両方の半分に表示される重複行が自動的に削除されます。

データ照合パターン

FULL OUTER JOINは2つのデータセットを比較するための標準的なツールです：

SELECT
  COALESCE(a.id, b.id) AS id,
  CASE
    WHEN a.id IS NULL THEN 'Missing in A'
    WHEN b.id IS NULL THEN 'Missing in B'
    ELSE 'Present in both'
  END AS status
FROM dataset_a AS a
FULL OUTER JOIN dataset_b AS b ON a.id = b.id;

これにより、各側に欠落しているレコードが即座にわかります。