結合や副問い合わせを使ったデータ操作

複数のテーブルを使って結果を得る方法について記載していきます。

1つのテーブルからデータを取得する方法と異なり、SQLとして記述する内容が多くなり複雑になってきます。

それぞれの処理がどのようなことを行っているのか、それによってどのような結果になるのかをイメージして読み解いて頂ければと思います。

1. 複数テーブルを結合したデータ表示


業務などでSQLを使う場合に1つのテーブルのみで処理が簡潔することはあまりなく、複数のテーブルを繋ぎ合わせて1つの結果とすることが多いかと思います。

その時にSQLの集合機能を使用してテーブルを繋ぎ合わせることで期待する結果を得ることができます。

1-1. テーブル結合の種類

ここで使用するテーブル結合の種類は、大きく分けて次の通りになります。

・内部結合（INNER JOIN）

・外部結合（OUTER JOIN）


また、外部結合には、結合方式が3つ存在しています。

・左外部結合（LEFT OUTER JOIN）

・右外部結合（RIGHT OUTER JOIN）

・完全外部結合（FULL OUTER JOIN）

1-2. 内部結合を使ったテーブル結合

テーブルを結合する際にテーブルを紐づける条件（結合条件）を使用して条件に一致する結果のみを返します。

結合条件に一致しないレコードは返されません。


以下の例では、社員テーブルと部署テーブルを内部結合して部署名を紐づけた結果を取得しています。

ここで「社員NO：3、部署CD：A2、名前：三郎」のレコードが除外された結果が取得されています。


社員テーブルに「部署CD：A2」が存在しているが、部署テーブルに「部署CD：A2」が存在していないため、結合条件が「=（イコール）」ならない（結合条件を満たしていない）として除外されています。


内部結合を行う構文は、次の通りです。

場合によって「INNER JOIN」や「JOIN」と記述していることもありますが、どちらも同じ内部結合になるため、どちらの記述でも問題ありません。

FROM句の後に記載したテーブルが左側に該当し、INNER JOINの後に記載したテーブルが右側に該当します。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    INNER JOIN <テーブル名2>
    ON <テーブル名1>.<列名> = <テーブル名2>.<列名>;

1-3. 外部結合を使ったテーブル結合

外部結合では、結合方式が3つありますが、それぞれの違いやイメージを次の通りに記載します。

・左外部結合（LEFT OUTER JOIN）

結合対象になるテーブルのうち左側のテーブルを軸に結合条件に満たしていないデータも含めて表示します。

結合条件に満たしている場合は、右側のテーブルにあるデータが表示されますが、結合条件に満たしていない場合は、NULLが表示されます。


以下の例では、社員テーブルと部署テーブルを左外部結合して部署名を紐づけた結果を取得しています。

ここで「社員NO：3、部署CD：A2、名前：三郎」のレコードのみ部署名がNULLになっています。

部署テーブルに「部署CD：A2」に該当するレコードが存在しなかったため、NULLとして結果が取得されています。


左外部結合を行う構文は、次の通りです。

OUTERの記述は省略可能です。

FROM句の後に記載したテーブルが左側に該当し、LEFT OUTER JOINの後に記載したテーブルが右側に該当します。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    LEFT OUTER JOIN <テーブル名2>
    ON <テーブル名1>.<列名> = <テーブル名2>.<列名>;

・右外部結合（RIGHT OUTER JOIN）

結合対象になるテーブルのうち右側のテーブルを軸に結合条件に満たしていないデータも含めて表示します。

動作としては、左外部結合と同様の動作になります。


以下の例では、社員テーブルと部署テーブルを右外部結合して部署名を紐づけた結果を取得しています。

右側の軸になるテーブル「部署CD：D1、部署名：営業部」に紐づくレコードが左側のテーブルに存在していないため、社員テーブルの結果がNULLになっています。


また、社員テーブルの「社員NO：3、部署CD：A2、名前：三郎」と「社員NO：4、部署CD：C1、名前：四郎」は、部署テーブルの部署CDと「=（イコール）」ならない（結合条件を満たしていない）として除外されています。


右外部結合を行う構文は、次の通りです。

OUTERの記述は省略可能です。

FROM句の後に記載したテーブルが左側に該当し、RIGHT OUTER JOINの後に記載したテーブルが右側に該当します。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    RIGHT OUTER JOIN <テーブル名2>
    ON <テーブル名1>.<列名> = <テーブル名2>.<列名>;

・完全外部結合（FULL OUTER JOIN）

結合対象になる左右のテーブルを軸に結合条件に満たしていないデータも含めて表示します。左外部結合と右外部結合の両方の動作を行います。

以下の例では、左外部結合で行った結果と右外部結合で行った結果を含めて取得しています。


完全外部結合を行う構文は、次の通りです。

OUTERの記述は省略可能です。

FROM句の後に記載したテーブルが左側に該当し、FULL OUTER JOINの後に記載したテーブルが右側に該当します。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    FULL OUTER JOIN <テーブル名2>
 &

1-4. その他の結合

・USINGを使った結合

結合するテーブルで同じ名前のカラムを結合する場合、USING句を使って結合することができます。

ただし結合できるのは「同じ名前のカラム」を結合する場合に限ります。


カラム名が異なる場合は、ON句を使って結合します。

例として、社員テーブルの「部署CD」と部署テーブルの「部署CD」を比較する場合にUSING句が使用できます。


ON句を使った結合の場合とUSING句を使った結合の場合でSQLを比較します。

どちらも同じ処理になりますが、可読性が変わってきます。

・ON句を使った結合の場合

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    INNER JOIN <テーブル名2>
    ON <テーブル名1>.<列名> = <テーブル名2>.<列名>;


・USING句を使った結合

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    INNER JOIN <テーブル名2>
    USING(<列名>);

・NATURAL JOIN（自然結合）を使った結合

結合するテーブルで同じカラム名かつ同じデータ型の場合に自然結合を使ってテーブルを結合できます。


ON句やUSING句で結合するカラム名を指定しましたが、自然結合では指定しなくても同じカラム名、同じデータ型のカラムが自動的に選択されます。

また、同じカラム名、同じデータ型が複数存在している場合、同じカラム全てが選択されます。


自然結合を行う構文は、次の通りです。

カラム名が異なる、データ型が異なる場合は、ON句を使って結合する必要があります。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    NATURAL INNER JOIN <テーブル名2>

・複数のテーブル結合

これまでは、2つのテーブルを結合する方法について記載しましたが、3つ以上のテーブルを結合することも可能です。

結合時の動作は、これまでの内容を確認してください。

複数のテーブルを結合する構文は、次の通りです。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    INNER JOIN <テーブル名2>
    ON <テーブル名1>.<列名> = <テーブル名2>.<列名>
    INNER JOIN <テーブル名3>
    ON <テーブル名2>.<列名> = <テーブル名3>.<列名>;

2. 副問い合わせ

SELECT文の中にSELECT文を埋め込むことを副問い合わせ（サブクエリ）と呼びます。

これによりSQLを1度実行することで目的の結果を得ることができます。


例として、平均勤務時間以上の名前を抽出する場合を考えます。

勤務時間の平均を求めてから、それぞれの勤務時間が平均より大きいか判断して結果を抽出します。この処理をSQLで実行した場合、次の通りになります。

SELECT 社員NO, 名前, 勤務時間 FROM 勤怠テーブル
    WHERE 勤務時間 > (SELECT AVG(勤務時間) FROM 勤怠テーブル);


2-1. スカラー副問い合わせ

副問い合わせの結果が「1行で1列の値」の結果を返すサブクエリになります。

上記（平均勤務時間以上の名前を抽出する）の例では、サブクエリの結果が平均値を返す処理になっています。

そのため、以下の図のようにサブクエリから返される結果が「1行で1列の値」となっています。


・スカラー副問い合わせの注意点

●複数の行で1列の値を返す

サブクエリの結果が「複数の行で1列の値」を返す場合、「1行で1列の値」がサブクエリの期待値になるため、エラーとなります。

ただし、次項の非スカラー副問い合わせを使うことでエラーとならずに処理を行えます。

●1行で複数の列の値を返す

サブクエリの結果が「1行で複数の列」を返す場合も同様にエラーとなります。

●サブクエリの結果が0行を返す

サブクエリで抽出した結果が0行の場合、値が何も返されないためNULLと比較すると同じような結果になります。

エラーとなることはありませんが、NULLに対してISNULL条件以外での検索条件になるため、SELECT文の結果は何も返されません。

2-2. 非スカラー副問い合わせ

副問い合わせの結果が「1行で1列の値」以外の結果を返すサブクエリになります。

サブクエリが返す結果によって次の通りに分類されます。

・「複数の行で1列の値」を返すサブクエリ

・「1行で複数の列の値」を返すサブクエリ

・「複数の行で複数の列の値」を返すサブクエリ


これらの処理を適切に行うには、サブクエリの結果を受け取る側で値を処理できるようにする必要があります。

WHERE句の条件で複数の値を処理できる演算子を使って条件を作成します。

2-3. 「複数の行で1列の値」を返す場合

・INを使ったサブクエリ

WHERE句内でINを使うことで複数の値のいずれかと等しい結果を取得することができます。

INを使用することで『「複数の行で1列の値」を返すサブクエリ』の結果からいずれかと等しい結果を取得することができます。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    WHERE <列名> IN (
 SELECT <列名1> FROM <テーブル名1> WHERE <列名> = <条件>);

・ANYを使ったサブクエリ

ANYを使うことで複数の値のいずれかが、直前に指定した演算子（「=」、「<」など）を満たす場合に結果を返します。

例として、「<列名> = ANY(A, B, C)」の場合、列名の値がAかBかCのいずれかと等しい場合に結果を返す処理を行います。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    WHERE <列名> ANY (
 SELECT <列名1> FROM <テーブル名1> WHERE <列名> = <条件>);

・ALLを使ったサブクエリ

ALLを使うことで複数の値が全て直前に指定した演算子（「=」、「<」など）を満たす場合に結果を返します。

例として、「<列名> = ALL(A, B, C)」の場合、列名の値がAかBかCの全てと等しい場合に結果を返す処理を行います。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    WHERE <列名> ALL (
 SELECT <列名1> FROM <テーブル名1> WHERE <列名> = <条件>);

2-4. 「1行で複数の列の値」を返す場合

「1行で複数の列の値」を返すサブクエリに対してWHERE句の左側の列数とデータ型が、右側のサブクエリが返す列数とデータ型が同じ場合に処理を行うことができます。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
    WHERE (<列名A>, <列名B>) =
( SELECT <列名A>, <列名B> FROM <テーブル名1> WHERE <列名> = <条件>);

3. 集合演算

集合演算を行うことで複数件の問い合わせ結果を組み合わせて1つの結果とすることができます。

集合演算を行った問い合わせのことを複合問い合わせと呼びます。

3-1. 集合演算子の種類

ここで使用する集合演算子の種類は、次の通りになります。

・UNIONによる和集合

・UNION ALLによる和集合

・INTERSECTによる積集合

・MINUSによる差集合


集合演算を行う構文は、それぞれ同じになるため、以下にまとめて記載します。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
  [ UNION | UNION ALL | INTERSECT | MINUS ]
SELECT <列名1>, <列名2>, ... FROM <テーブル名2>

・UNIONとUNION ALLによる和集合

UNIONとUNION ALL演算子は、和集合を返します。

UNION演算子は、重複する行を含めずに結果を返しますが、UNION ALL演算子は、重複する行も含めて結果を返します。

・UNION
テーブルAとテーブルBを重複含めずに結果を表示しています。


・UNION ALL
テーブルAとテーブルBを重複含めて結果を表示しています。


・INTERSECTによる積集合

INTERSECT演算子は、積集合を返します。

テーブルＡとテーブルＢで結果が一致する行を重複を含まないで返します。


・MINUSによる差集合

MINUS演算子は、差集合を返します。

テーブルAとテーブルBを比較してテーブルAにしか含まれていない行を返します。

MINUS演算子では、問い合わせの順番により結果が変わってきます。

テーブルAとテーブルBを比較する場合は、テーブルAの結果が返され、テーブルBとテーブルAを比較する場合は、テーブルBの結果が返されます。

3-2. 使用する際の注意事項

・列数とデータ型の一致

集合演算では、SELECTリスト（SELECT文の後に記載するカラム名）に指定した数とデータ型が各問い合わせで一致している必要があります。

SELECTリストの数が異なっている場合、エラーとなります。

SELECTリストの数が異なる場合、不足している側でNULLや同じデータ型のリテラルを指定すればエラーを回避できます。


SELECTリストの数は同じでもデータ型が異なる場合は、型変換を行いデータ型を同じにする必要があります。

集合演算では、暗黙的データ型変換は行われません。

・ORDER BY句の記述位置

集合演算で取得結果をソートする場合、ORDER BY句を実行するSQL文の一番最後に記述します。

それ以外の場所でORBER BY句を記述した場合、エラーとなります。

SELECT <列名1>, <列名2>, ... FROM <テーブル名1>
  [ UNION | UNION ALL | INTERSECT | MINUS ]
SELECT <列名1>, <列名2>, ... FROM <テーブル名2>
ORDER BY <列名1>

UNION ALL演算子以外の集合演算では、ORDER BY句を記述しなくても返される結果が昇順でソートされます。

ただし、使用しているRDBやバージョンアップなどで動作が変わってくるため、注意が必要です。

そのため、ソートを行う場合は、明示的にORDER BY句を記述した方がよいです。

4. まとめ

テーブル結合や副問い合わせを使ったデータ操作について記載しました。

テーブル結合や副問い合わせは、業務でよく登場するSQLになるため、使い方を知ることで業務に役立つかと思います。

業務によく登場する分、複雑な構成になっている場合もあるため、まずは、簡単なSQLを実際に動かしてどのような結果が返ってくるのかなど操作に慣れていく必要もあると思っています。

人気のタグ一覧

関連記事

カテゴリー