SQL の内部結合(INNER JOIN)と内部結合 (OUTER JOIN)

ほんとに何回やっても覚えられないので、ここにまとめる。

前提

• MSSQL (SQL Server 2019) を使う
• 記事の最後のほうにまとめてあるサンプルデータをデータベースに投入してあるものとする

内部結合と外部結合

内部結合は両辺の積集合、外部結合は両辺の和集合である。私はこれで遂に大筋の理解ができたものの、あんまり最初の方にこれを言ってくれる文書がなかったので、本記事では最初に言っておく。

※ 積集合は $A$ と $B$ の集合があるとして、$A$ と $B$ に共通する要素だけを集めた集合である。和集合は $A$ か $B$ のどちらかまたは両方に存在する要素を集めた集合である。

ON について

内部結合にせよ外部結合にせよ、ON でテーブルの左辺（SELECT FROM で指定する方）と右辺（JOIN で結合したい方）を接続する基準となる列を指定する。

INNER JOIN

INNER JOIN は両辺のテーブルについて右辺と左辺の積を取る。ON で示される結合基準に基づき、左辺と右辺のどちらかのデータが存在しなければ、その列は除去される。

SELECT
    users.username,
    users.country,
    countries.gdp,
    countries.population
FROM users
    INNER JOIN countries ON users.country = countries.country

OUTER JOIN

OUTER JOIN は両辺の和を取る。つまり、片方の辺にしかデータが存在しない場合でも、その列は保持されたままデータを取り出せる。「片方の辺」の基準は LEFT か RIGHT で指定する。なお、存在しないデータは NULL が充当される。

LEFT OUTER JOIN

LEFT OUTER JOIN は左辺（SELECT ... FROMで選ぶ方）の列を基準として両辺の和を取る。左辺にしかないデータは保持される一方で、右辺にしかないデータは除去される。

SELECT *
FROM users
    LEFT OUTER JOIN countries ON users.country = countries.country

RIGHT OUTER JOIN

RIGHT OUTER JOIN は右辺 (RIGHT OUTER JOIN で選ぶ方）の列を基準として両辺の和を取る。右辺にしかないデータは保持される一方で、左辺にしかないデータは除去される。

SELECT *
FROM users
    RIGHT OUTER JOIN countries ON users.country = countries.country

FULL OUTER JOIN

例えば LEFT OUTER JOIN は左辺にしかないデータだけは保持される一方で、右辺にないデータは除去されていた。このとき、右辺にしかないデータも保持したいときがある。

このときに用いるのが FULL OUTER JOIN である。FULL OUTER JOIN では左辺にしかないデータでも右辺にしかないデータでも除去されずに保持される。

SELECT *
FROM users
    FULL OUTER JOIN countries ON users.country = countries.country

メモ: サンプルデータの作成

MSSQL でやっている。面倒なことは面倒な奴に任せるべきなので、テストデータは ChatGPT を使って生成した。