MySQLでFULL OUTER JOIN (完全外部結合)を行う

はじめに

テーブル結合と言えば、INNER JOIN か LEFT OUTER JOIN 位しか使用した事が無かったのですが、先日、FULL OUTER JOINを使いたいタイミングがありました。

滅多に使わないと思うので備忘録として使い方を残しておきたいと思います。

FULL OUTER JOIN とは

結合する2つのテーブル全ての列（レコード）を抽出する結合方法です。結合条件に当てはまるデータは1つの列にまとめられ、結合条件に当てはまらない場合は片方のテーブルの値はNullになります。
別の言い方をすると、LEFT OUTER JOINの結果とRIGHT OUTER JOINの結果を合わせた（重複は除く）ものと同じ結果が得られます。

具体例で見てみます。サッカー選手と所属チームのテーブルを用意します。

RDMBSはPostgreSQLを使用しています。

選手テーブル

チームテーブル

この2つのテーブルをFULL OUTER JOINしてみます。
SELECT soccer_players.name, soccer_players.team_id, soccer_teams.name FROM soccer_players FULL OUTER JOIN soccer_teams ON soccer_players.team_id = soccer_teams.id

結合条件と合致しない列（久保建英とミラン）も両テーブルからNULLを含めて取得されています。

結果

MySQLでは

MySQLでは FULL OUTER JOIN 構文が使えないので、LEFT OUTER JOIN と RIGHT OUTER JOIN と UNION を組み合わせて対応します。
LEFT OUTER JOIN の結果と RIGHT OUTER JOIN の結果をUNIONで結合させてます。

SELECT sp.name, sp.team_id, st.name
FROM soccer_players sp
LEFT OUTER JOIN soccer_teams st
ON sp.team_id = st.id
UNION
SELECT sp.name, sp.team_id, st.name
FROM soccer_players sp
RIGHT OUTER JOIN soccer_teams st
ON sp.team_id = st.id
;

CROSS JOIN

おまけにCROSS JOINも使った事が無かったので試してみました。
これは2つのテーブルの列の全ての組み合わせ結果を取得する事が出来ます。

SELECT sp.name, sp.team_id, st.name
FROM soccer_players sp
CROSS JOIN soccer_teams st
;

上記の例で言うと、以下の結果が得られます。

さいごに

FULL OUTER JOIN と CROSS JOIN を試してみました。

使う機会は少ないと思いますが、どなたかの参考になれば。

おすすめ書籍