複数のテーブルを結合してデータを取得する JOIN [SQLServer]

SQL Server ロゴ

SQLServerを含むリレーショナルデータベースでは、データを管理する際にテーブルを分けます。

データを管理に適した形態の複数のテーブルに分けることで、データを安全、確実に管理することができます。

テーブルを分けるメリットについては、以下の記事を参照してください。

複数のテーブルに分けて管理するメリットとデメリット [SQLServer]
データベースでは、テーブルはできるだけ分けて管理する方がよいと言われています。 テーブルを複数に分けることにより、データを安全...

テーブルのデータは、データベースが管理しやすい形態で設定しますが、データベースの管理に適した形のデータは、必ずしも人間が理解しやすいものではありません。
そこでデータベースには、データベースのデータを人間が理解しやすい形にするために、テーブルを結合する機能が用意されています。

本記事では、SQLServerで使えるテーブルの結合について記載します。

テーブルの結合

SQLServerなどのデータベースでは、テーブルを結合するためにJOINというキーワードを使用します。
JOINキーワードは、SQLのSELECT文を使用してデータを取得する際に、テーブルとテーブルの間に記述します。
そして、テーブルを結合するキーとなる列を指定して、テーブル同士を結び付けます。

例えば、商品を管理する商品テーブルと、商品の売り上げ伝票を管理する売上伝票テーブルがあるとします。

商品テーブル
商品ID 商品名 価格
1001 商品 A 3,800
1002 商品 B 4,600
1003 商品 C 1,200
2001 商品 D 5,000
2002 商品 E 2,500
売上伝票テーブル
伝票ID 日付 商品 数量
10001 2010/10/15 1001 153
10002 2010/10/23 2001 212
10003 2010/11/03 1003 123
10004 2010/11/12 1001 186
10005 2010/11/18 1003 204
10006 2010/11/25 1002 179
10007 2010/12/10 2002 108
10008 2010/12/21 2001 237

この商品テーブルと売上伝票テーブルを結合すると以下のようになります。

商品テーブルと売上伝票テーブルを結合
商品テーブルと売上伝票テーブルは、商品IDの列で関連付けられて結合します。(商品IDがテーブル動詞を結合するキーになります。)

SQLのSELECT文で商品テーブルと売上伝票テーブルを結合を記述すると以下のようになります。
ここでは例として、売上伝票テーブルのすべての列と、商品テーブルの商品名の列を取得します。

上記のSQLを実行すると、以下のような結果表が取得できます。

伝票ID 日付 商品ID 商品名 数量
10001 2010/10/15 1001 商品 A 153
10002 2010/10/23 2001 商品 D 212
10003 2010/11/03 1003 商品 C 123
10004 2010/11/12 1001 商品 A 186
10005 2010/11/18 1003 商品 C 204
10006 2010/11/25 1002 商品 B 179
10007 2010/12/10 2002 商品 E 108
10008 2010/12/21 2001 商品 D 237

2つのテーブル(AテーブルとBテーブル)を結合する時のSELECT文は

で表すことができます。

[テーブルから選択する列のリスト]には、テーブルから取得する列を指定します。複数の列がある場合はカンマで区切ります。
[両方のテーブルを結合する条件式]には、テーブルのどの列を条件に結合するかを指定します。
結合する列が複数ある場合はONの後にANDを続けて次のように記述します。

テーブル結合の種類

SQLServerにはテーブルを結合する方法に、以下の5種類が用意されています。

  1. 内部結合
  2. 左外部結合
  3. 右外部結合
  4. 完全外部結合
  5. 交差結合

使用するテーブル

以下の記事では、テーブルの結合結果を示す例として、次のテーブルを利用します。

テーブルの列定義

特別会員テーブル
列名 データ型 主キー
特別会員ID int 1
特別会員名 nvarchar(50)
優待会員テーブル
列名 データ型 主キー
優待会員ID int 1
優待会員名 nvarchar(50)

テーブルのデータ

特別会員テーブル
特別会員ID 特別会員名
2 特別会員2
3 特別会員3
優待会員テーブル
優待会員ID 優待会員名
1 優待会員1
3 優待会員3

テーブル作成SQL

特別会員テーブル

優待会員テーブル

データ挿入SQL

特別会員テーブル

優待会員テーブル

内部結合

内部結合は、テーブル同士を結合する条件式に指定した列の値を照合し、両方のテーブルで一致するレコード(行)のみを抜き出します。
結合する相手のレコードが存在しない場合は、レコードは抽出対象から除外されます。

内部結合のイメージを図で表すと以下のようになります。

INNER JOIN 内部結合

SQLでは「INNER JOIN」というキーワードを使用してテーブルを内部結合します。

特別会員テーブルと優待会員テーブルを内部結合した場合の例を示します。

上記のSQLを実行して得られる結果は以下のようになります。

特別会員ID 特別会員名 優待会員ID 優待会員名
3 特別会員3 3 優待会員3

特別会員テーブルと優待会員テーブルの両方に存在する会員IDが「3」のレコードのみ抽出対象となります。

テーブルを結合するときにJOINのみ記述(INNERを省略)すると、内部結合になります。(INNER JOINと記述した場合と同じ結果になります。)

売上伝票テーブルと商品テーブルを結合する際にサンプルとして示したSQLの

というSQLは

と記述した場合と同じになります。

左外部結合

左外部結合は、テーブルを結合する条件式に指定した列の値を照合し、左側のテーブル(先に記述したテーブル)のレコードはすべて抜き出し、右側のテーブル(後に記述したテーブル)のレコードのうち、条件に一致するもののみ結合します。
結合する相手のレコードが存在しない場合でも、左側のテーブルのレコードはすべて抽出対象になります。

左外部結合のイメージを図で表すと以下のようになります。

LEFT OUTER JOIN 左外部結合

SQLでは「LEFT OUTER JOIN」というキーワードを使用してテーブルを左外部結合します。

「LEFT OUTER JOIN」の「OUTER」は省略可能なので、「LEFT JOIN」と記述することもできます。

特別会員テーブルと優待会員テーブルを左外部結合した場合の例を示します。

上記のSQLを実行して得られる結果は以下のようになります。

特別会員ID 特別会員名 優待会員ID 優待会員名
2 特別会員2 NULL NULL
3 特別会員3 3 優待会員3

左側の特別会員テーブルのレコードは、すべて抽出対象になります。
結合先の優待会員テーブルには会員IDが「3」のレコードがありますので、優待会員テーブルの会員IDが「3」のレコードが結合されます。
結合できない会員IDが「2」のレコードでは、優待会員テーブルのデータの部分が「NULL」になります。

右外部結合

右外部結合は、左外部結合とは逆に、右側のテーブル(後に記述したテーブル)のレコードはすべて抜き出し、左側のテーブル(先に記述したテーブル)のレコードのうち、条件に一致するもののみ結合します。
結合する相手のレコードが存在しない場合でも、右側のテーブルのレコードはすべて抽出対象になります。

右外部結合のイメージを図で表すと以下のようになります。

RIGHT OUTER JOIN 右外部結合

SQLでは「RIGHT OUTER JOIN」というキーワードを使用してテーブルを右外部結合します。

「RIGHT OUTER JOIN」の「OUTER」も「LEFT OUTER JOIN」と同様に省略可能なので、「RIGHT JOIN」と記述することができます。

特別会員テーブルと優待会員テーブルを右外部結合した場合の例を示します。

上記のSQLを実行して得られる結果は以下のようになります。

特別会員ID 特別会員名 優待会員ID 優待会員名
NULL NULL 1 優待会員1
3 特別会員3 3 優待会員3

右側の優待会員テーブルのレコードは、すべて抽出対象になります。
結合先の特別会員テーブルには会員IDが「3」のレコードがありますので、特別会員テーブルの会員IDが「3」のレコードが結合されます。
結合できない会員IDが「1」のレコードでは、特別会員テーブルのデータの部分が「NULL」になります。

完全外部結合

完全外部結合は、結合する両方のテーブルのすべての行を抜き出し、結合条件に一致するもののみ結合します。
結合する相手のレコードが存在しない場合でも、左右両方のテーブルのすべてのレコードが抽出対象になります。

完全外部結合のイメージを図で表すと以下のようになります。

FULL OUTER JOIN 完全外部結合

SQLでは「FULL OUTER JOIN」というキーワードを使用してテーブルを完全外部結合します。

「FULL OUTER JOIN」の「OUTER」も省略可能なので、「FULL JOIN」と記述することができます。

特別会員テーブルと優待会員テーブルを完全外部結合した場合の例を示します。

上記のSQLを実行して得られる結果は以下のようになります。

特別会員ID 特別会員名 優待会員ID 優待会員名
NULL NULL 1 優待会員1
2 特別会員2 NULL NULL
3 特別会員3 3 優待会員3

左側の特別会員テーブルと右側の優待会員テーブルのすべてのレコードが抽出対象になります。
結合可能なレコードが結合され、結合できない場合はデータの部分が「NULL」になります。

交差結合

交差結合は、結合する2つのテーブルのレコードの総当たりの組み合わせのレコードを生成して抜き出します。
交差結合では、すべてのレコードの組み合わせが生成されるので、結合する条件となる列は必要ありません。

交差結合のイメージを図で表すと以下のようになります。

結合の組み合わせ

CROSS JOIN 交差結合

結合した結果

CROSS JOIN 結合結果表

SQLでは「CROSS JOIN」というキーワードを使用してテーブルを交差結合します。

特別会員テーブルと優待会員テーブルを交差結合した場合の例を示します。

上記のSQLを実行して得られる結果は以下のようになります。

特別会員ID 特別会員名 優待会員ID 優待会員名
2 特別会員2 1 優待会員1
3 特別会員3 1 優待会員1
2 特別会員2 3 優待会員3
3 特別会員3 3 優待会員3

左側の特別会員テーブルと右側の優待会員テーブルのすべてのレコードを組み合わせた結果が抽出されます。

補足

テーブルの内部結合は、JOINキーワードを使用せずに、以下のようなSQLでも記述できます。

FROM句に指定するテーブルをカンマで区切って記述します。
そしてWHERE句にテーブルを結合する条件となる式を記述します。

テーブルの交差結合は、JOINキーワードを使用せずに、以下のようなSQLでも記述できます。

交差結合では、内部結合の[両方のテーブルを結合する条件式]を省いた形になります。