SQL練習室
← SQLガイド

SQLのFULL OUTER JOINとは?両方の未一致を残す方法と差分の見つけ方

FULL OUTER JOINは、左右どちらの表にしかない行も残す結合です。一致した行は1行にまとまり、一致しない行はもう一方の列がNULLになります。4月と5月の会員名簿を突き合わせ、継続・退会・新規を見分けながら、LEFT JOIN・RIGHT JOINとの違いを確認しましょう。

例で使う表

april_membersは4月の会員、may_membersは5月の会員で、1行が会員1人、idは会員番号です。青木(1)と大和(4)は4月だけ、村上(5)と白石(6)は5月だけにいます。北野(2)と中央(3)は両方にいます。ORDER BYのない結果の行順は一例で、保証されません。

april_members:4月の会員4人
idname
1青木
2北野
3中央
4大和
may_members:5月の会員4人
idname
2北野
3中央
5村上
6白石

FULL OUTER JOINの基本構文

sql
SELECT a.id AS april_id, m.id AS may_id
FROM april_members AS a
FULL OUTER JOIN may_members AS m
  ON a.id = m.id;

FULL OUTER JOINで両方の表の全行を並べる

FULL OUTER JOINは、ONで一致する行を1行にまとめたうえで、一致しない行を左右どちらも残します。北野と中央は両方に入り、青木と大和は5月側がNULL、村上と白石は4月側がNULLになります。4月と5月の会員を合わせた6人が1行ずつ並びます。

sql
SELECT a.id AS april_id, a.name AS april_name, m.id AS may_id, m.name AS may_name
FROM april_members AS a
FULL OUTER JOIN may_members AS m
  ON a.id = m.id
ORDER BY COALESCE(a.id, m.id);
実行結果
april_idapril_namemay_idmay_name
1青木NULLNULL
2北野2北野
3中央3中央
4大和NULLNULL
NULLNULL5村上
NULLNULL6白石

COALESCEで左右のキーを1列にまとめる

FULL OUTER JOINの結果では、会員番号が4月側と5月側の2列に分かれ、どちらかがNULLの行があります。COALESCE(a.id, m.id)は、左の値があれば左、なければ右を返すので、全員の会員番号を1列にまとめられます。名前も同じように書くと、6人の一覧になります。

sql
SELECT COALESCE(a.id, m.id) AS id, COALESCE(a.name, m.name) AS name
FROM april_members AS a
FULL OUTER JOIN may_members AS m
  ON a.id = m.id
ORDER BY id;
実行結果
idname
1青木
2北野
3中央
4大和
5村上
6白石

片方にしかない行をIS NULLで抜き出す

結合後に5月側のidがNULLの行だけを残すと、4月にいて5月にいない会員が分かります。青木と大和の2人です。判定には、結合キーのように必ず値が入る列を使います。5月側の列がNULLなのは、結合で一致する行が見つからなかったためです。逆にa.id IS NULLなら、5月だけの会員です。

sql
SELECT a.id, a.name
FROM april_members AS a
FULL OUTER JOIN may_members AS m
  ON a.id = m.id
WHERE m.id IS NULL
ORDER BY a.id;
実行結果
idname
1青木
4大和

どちらか一方にしかない行を1回で取り出す

4月だけと5月だけを一度に調べるには、WHEREでa.id IS NULL OR m.id IS NULLと書きます。両方にいる北野と中央は除かれ、青木、大和、村上、白石の4人が残ります。CASEで、どちらにいたかの区分を付けると、結果が読みやすくなります。FULL OUTER JOINだけで、2つの表の差分を両方向で検出できます。

sql
SELECT COALESCE(a.id, m.id) AS id,
       COALESCE(a.name, m.name) AS name,
       CASE WHEN m.id IS NULL THEN '4月のみ' ELSE '5月のみ' END AS status
FROM april_members AS a
FULL OUTER JOIN may_members AS m
  ON a.id = m.id
WHERE a.id IS NULL OR m.id IS NULL
ORDER BY id;
実行結果
idnamestatus
1青木4月のみ
4大和4月のみ
5村上5月のみ
6白石5月のみ

LEFT JOIN・RIGHT JOINとの行数の違い

LEFT JOINは左の表の全行、RIGHT JOINは右の表の全行を残し、FULL OUTER JOINは両方を残します。この例では、LEFT JOINが4月の4人で4行、RIGHT JOINが5月の4人で4行、FULL OUTER JOINが6行です。LEFT JOINだと5月だけの村上と白石が、RIGHT JOINだと4月だけの青木と大和が見えません。片側だけ見ると、反対側の差分を見落とします。

sql
SELECT
  (SELECT COUNT(*) FROM april_members AS a LEFT JOIN may_members AS m ON a.id = m.id) AS left_rows,
  (SELECT COUNT(*) FROM april_members AS a RIGHT JOIN may_members AS m ON a.id = m.id) AS right_rows,
  (SELECT COUNT(*) FROM april_members AS a FULL OUTER JOIN may_members AS m ON a.id = m.id) AS full_rows;
実行結果
left_rowsright_rowsfull_rows
446

FULL OUTER JOINでよくある間違い

片方の表の列でWHERE条件を書くと、未一致の行が消える

FULL OUTER JOINの後に、WHERE m.id >= 2のように片方の表の列で絞ると、NULLの行は条件を通りません。4月だけの青木と大和が消え、残るのは5月側に会員番号がある4人だけです。実質的にRIGHT JOINのような結果になり、差分を見失います。未一致を残したいときは、IS NULLも許す条件にするか、絞り込みを結合前に行います。

sql
SELECT a.name AS april_name, m.name AS may_name
FROM april_members AS a
FULL OUTER JOIN may_members AS m
  ON a.id = m.id
WHERE m.id >= 2;

実際に1問解いてみる

FULL OUTER JOIN

april_membersとmay_membersを会員番号で結合し、両方の表の全行について、4月側の名前をapril_name、5月側の名前をmay_nameとして取得してください。行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

FULL OUTER JOINについてよくある質問

FULL OUTER JOINはどんなときに使いますか?
2つの表を突き合わせて、どちらかにしかない行も含めて一覧したいときに使います。月ごとの名簿、旧システムと新システムのデータ、予算と実績の比較などです。
LEFT JOINとRIGHT JOINのどちらかで足りますか?
片方の向きの差分だけなら足ります。4月にいて5月にいない会員だけならLEFT JOINとIS NULLで足ります。ただし両方向を一度に調べるなら、FULL OUTER JOINの方が簡単です。
結合キーを1列にまとめるにはどうしますか?
COALESCE(a.id, m.id)のように、左右の列を並べて、NULLでない方を使います。一致した行は両方の値が同じなので、どちらを取っても同じ値になります。
FULL OUTER JOINの結果の行数はどう決まりますか?
一致した行の組と、左だけの行、右だけの行の合計です。この例では、一致が2組(北野・中央)、4月だけが2人、5月だけが2人で6行です。1対多の関係で1つの行が複数に一致するときは、その分だけ行が増えます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。