はじめに
テーブル結合と言えば、INNER JOIN か LEFT OUTER JOIN 位しか使用した事が無かったのですが、先日、FULL OUTER JOINを使いたいタイミングがありました。
滅多に使わないと思うので備忘録として使い方を残しておきたいと思います。
FULL OUTER JOIN とは
結合する2つのテーブル全ての列（レコード）を抽出する結合方法です。結合条件に当てはまるデータは1つの列にまとめられ、結合条件に当てはまらない場合は片方のテーブルの値はNullになります。
別の言い方をすると、LEFT OUTER JOINの結果とRIGHT OUTER JOINの結果を合わせた（重複は除く）ものと同じ結果が得られます。
具体例で見てみます。サッカー選手と所属チームのテーブルを用意します。
RDMBSはPostgreSQLを使用しています。
選手テーブル
| 選手ID | 選手名 | チームID |
| 1 | 中田英寿 | 1 |
| 2 | 中村俊介 | 2 |
| 3 | 香川真司 | 3 |
| 4 | 久保建英 | 10 |
チームテーブル
| チームID | チーム名 |
| 1 | ローマ |
| 2 | セルティック |
| 3 | マンチェスター |
| 4 | ミラン |
この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を含めて取得されています。
結果
| 選手名 | チームID | チーム名 |
| 中田英寿 | 1 | ローマ |
| 中村俊介 | 2 | セルティック |
| 香川真司 | 3 | マンチェスター |
| 久保建英 | 10 | NULL |
| NULL | NULL | ミラン |
MySQLでは
MySQLでは FULL OUTER JOIN 構文が使えないので、LEFT OUTER JOIN と RIGHT OUTER JOIN と UNION を組み合わせて対応します。
LEFT OUTER JOIN の結果と RIGHT OUTER JOIN の結果をUNIONで結合させてます。
| 1 2 3 4 5 6 7 8 9 10 | 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つのテーブルの列の全ての組み合わせ結果を取得する事が出来ます。
| 1 2 3 4 | SELECT sp.name, sp.team_id, st.name FROM soccer_players sp CROSS JOIN soccer_teams st ; |
上記の例で言うと、以下の結果が得られます。
| 選手名 | チームID | チーム名 |
| 中村俊介 | 2 | ローマ |
| 中村俊介 | 2 | セルティック |
| 中村俊介 | 2 | マンチェスター |
| 中村俊介 | 2 | ミラン |
| 中田英寿 | 1 | ローマ |
| 中田英寿 | 1 | セルティック |
| 中田英寿 | 1 | マンチェスター |
| 中田英寿 | 1 | ミラン |
| 久保建英 | 10 | ローマ |
| 久保建英 | 10 | セルティック |
| 久保建英 | 10 | マンチェスター |
| 久保建英 | 10 | ミラン |
| 香川真司 | 3 | ローマ |
| 香川真司 | 3 | セルティック |
| 香川真司 | 3 | マンチェスター |
| 香川真司 | 3 | ミラン |
さいごに
FULL OUTER JOIN と CROSS JOIN を試してみました。
使う機会は少ないと思いますが、どなたかの参考になれば。
おすすめ書籍
