メインコンテンツへスキップ
見出し画像

第15回:集合演算を理解する(UNION・UNION ALL・EXCEPT・INTERSECT)

    はじめに

    SQLを書いていると、複数のSELECT文で取得した結果を、1つにまとめたい場面があります。

    例えば、次のようなケースです。

    • 2つの検索結果を1つにまとめたい

    • 一方には存在するが、もう一方には存在しないデータを調べたい

    • 両方の検索結果に共通するデータを取得したい

    こうした処理に使えるのが、集合演算です。

    集合演算は、SELECT文の実行結果を「集合」として扱い、それらを組み合わせる考え方です。

    今回は、SQLBoltのテーブルを使いながら、次の4つを練習します。

    • UNION:和集合

    • UNION ALL:重複を残した結合

    • EXCEPT:差集合

    • INTERSECT:積集合

    さらに、集合演算を理解するうえで知っておきたい「重複排除」「冪等性」「集合の相等性」「除算」についても整理します。

    目標は、SQLを見たときに、どのデータが結果に残るのかを頭の中でイメージできるようになることです。

    今回も、「息をするように書ける」状態を目指して練習していきましょう。


    1.今回使用するテーブル

    今回の演習では、SQLBoltのLesson 8に登場する2つのテーブルを使用します。

    SQLBolt Lesson 8

    なお、Lesson 8そのものが集合演算を学ぶレッスンというわけではありません。

    今回は、Lesson 8で使用できるテーブル構成とデータが集合演算の練習に適しているため、演習環境として利用します。

    Buildingsテーブル

    建物の名前と収容人数を管理するテーブルです。

    画像

    主な列は次の2つです。

    • building_name:建物名

    • capacity:収容人数

    Employeesテーブル

    社員の情報を管理するテーブルです。

    画像

    主な列は次の4つです。

    • role:職種

    • name:社員名

    • building:所属する建物

    • years_employed:勤続年数

    今回は、Buildingsの building_name とEmployeesの building を使用します。


    2.集合Aと集合Bを用意する

    集合演算を理解するため、最初に2つの集合を作ります。

    集合A:Buildingsに登録されている建物

    まず、Buildingsテーブルから建物名を取得します。

    SELECT building_name
    FROM buildings;

    実行結果

    画像

    取得できる建物は4つです。

    集合として表すと、

    A = {1e, 1w, 2e, 2w}

    となります。

    集合B:Employeesに登録されている所属建物

    次に、Employeesテーブルから社員の所属建物を取得します。

    SELECT DISTINCT building
    FROM employees;

    実行結果

    画像

    取得できる建物は2つです。

    B = {1e, 2w}

    ここでは DISTINCT を使用しています。

    Employeesテーブルには同じ建物に所属する社員が複数存在するため、そのまま取得すると同じ建物名が繰り返し表示されます。

    今回は集合演算を理解するため、重複を取り除いています。

    2つの集合を比較する

    ここまでの結果を整理すると、次のようになります。

    • 集合A:1e、1w、2e、2w

    • 集合B:1e、2w

    つまり、Bに含まれる建物は、すべてAにも含まれています。

    集合の記号では、

    B ⊆ A

    と表します。

    この関係を頭に入れておくと、これから実行する4つの集合演算の結果を予想しやすくなります。


    3.UNION:和集合

    最初は UNION です。

    UNION は、2つのSELECT文の実行結果を1つにまとめ、重複を取り除きます。

    SQL

    SELECT building_name
    FROM buildings
    
    UNION
    
    SELECT DISTINCT building
    FROM employees;

    実行結果

    画像

    取得されるのは、4つの建物です。

    なぜ4件になるのか?

    集合Aには、1e、1w、2e、2wがあります。

    集合Bには、1e、2wがあります。

    これらを合わせると、1eと2wが重複します。

    しかし、UNION は重複を取り除くため、最終的には4件になります。

    集合の記号で表すと、

    A ∪ B = {1e, 1w, 2e, 2w}

    です。

    ベン図で考える

    ベン図では、集合Aと集合Bの両方の領域を塗りつぶすイメージです。

    画像

    今回、集合Bは集合Aの内側にすべて含まれています。

    そのため、和集合の結果は集合Aと同じになります。

    覚えておきたいこと

    UNION は、複数の検索結果をまとめたいときに使用します。

    ただし、単純に行を追加するだけではなく、重複排除も行う点が重要です。


    4.UNION ALL:重複を残して結合する

    次は UNION ALL です。

    UNION とよく似ていますが、重要な違いがあります。

    UNION ALLは、重複を取り除きません。

    SQL

    SELECT building_name
    FROM buildings
    
    UNION ALL
    
    SELECT DISTINCT building
    FROM employees;

    実行結果

    画像

    今回は6件取得されます。

    なぜ6件になるのか?

    集合Aの4件と、集合Bの2件を、そのまま合わせるからです。

    • 集合A:4件

    • 集合B:2件

    • 合計:6件

    1eと2wは両方に存在しますが、重複排除されません。

    UNIONとの違い

    今回の結果は、次のようになります。

    • UNION:4件

    • UNION ALL:6件

    この違いは、実務でも重要です。

    例えば、複数の期間のデータをまとめる際、同じ値の行が存在しても、それぞれ別の実績として残したい場合があります。

    そのような場面で UNION を使用すると、意図せず行が減る可能性があります。

    重複を残す必要があるのか、取り除く必要があるのか。

    この判断が大切です。

    ベン図との関係

    通常のベン図は、同じ要素が何回出現するかまでは表現しません。

    そのため、UNION ALL を理解するときは、ベン図だけでなく、2つのSELECT結果をそのまま縦に連結するイメージも持つと分かりやすくなります。

    画像

    5.EXCEPT:差集合

    次は EXCEPT です。

    EXCEPT は、最初のSELECT文に存在し、次のSELECT文には存在しない行を取得します。

    SQL

    SELECT building_name
    FROM buildings
    
    EXCEPT
    
    SELECT DISTINCT building
    FROM employees;

    実行結果

    画像

    取得されるのは、1wと2eの2件です。

    なぜ2件になるのか?

    集合Aには、4つの建物があります。

    集合Bには、1eと2wがあります。

    AからBに含まれる建物を取り除くと、1wと2eが残ります。

    集合の記号で表すと、

    A − B = {1w, 2e}

    です。

    ベン図で考える

    ベン図では、集合Aのうち、集合Bと重ならない領域だけを塗りつぶすイメージです。

    画像

    EXCEPTでは順序が重要

    ここで、ぜひ覚えておきたい性質があります。

    EXCEPT は、SELECT文の順番を入れ替えると結果が変わることがあります。

    今回のAとBでは、

    A − B = {1w, 2e}

    ですが、

    B − A = ∅

    です。

    集合Bの要素はすべてAに含まれているため、BからAを引くと何も残りません。

    つまり、

    A − B と B − A は、一般に同じではありません。

    この点が、UNION や INTERSECT との重要な違いです。

    実務での使い道

    EXCEPT は、2つのデータを比較して差分を調べたいときに役立ちます。

    例えば、マスタには登録されているのに、別の一覧には存在しないコードを探す場合です。

    こうした差分確認のSQLを自然に書けるようになると、データ照合の作業にも応用できます。


    6.INTERSECT:積集合

    最後は INTERSECT です。

    INTERSECT は、2つのSELECT文の両方に存在する行を取得します。

    SQL

    SELECT building_name
    FROM buildings
    
    INTERSECT
    
    SELECT DISTINCT building
    FROM employees;

    実行結果

    画像

    取得されるのは、1eと2wの2件です。

    なぜ2件になるのか?

    集合Aと集合Bの両方に含まれる建物は、1eと2wです。

    集合の記号で表すと、

    A ∩ B = {1e, 2w}

    です。

    ベン図で考える

    ベン図では、集合Aと集合Bが重なる領域だけを塗りつぶすイメージです。

    画像

    実務での使い道

    INTERSECT は、2つの検索結果に共通するデータを確認したいときに使用できます。

    例えば、2つの条件をそれぞれ満たすコード一覧を取得し、その共通部分だけを調べるようなケースです。


    7.集合演算をまとめて整理する

    ここまでの4つを整理しましょう。

    今回の集合は、

    A = {1e, 1w, 2e, 2w}

    B = {1e, 2w}

    でした。

    それぞれの結果は次のとおりです。

    • UNION:AとBを合わせて重複排除 → 4件

    • UNION ALL:AとBを重複を残して連結 → 6件

    • EXCEPT:AにあってBにないもの → 2件

    • INTERSECT:AとBの両方にあるもの → 2件

    SQLを実行する前に、まず頭の中で結果を予想してみてください。

    その後、実際にSQLを実行し、ベン図と照らし合わせます。

    予想 → SQL実行 → 結果確認

    この繰り返しが、集合演算を自然に使えるようになるための練習になります。


    8.重複排除とパフォーマンス

    集合演算では、重複排除の有無が重要です。

    今回使用した演算子のうち、

    • UNION:重複を排除する

    • EXCEPT:重複を排除する

    • INTERSECT:重複を排除する

    • UNION ALL:重複を排除しない

    という違いがあります。

    重複排除には処理コストがかかる

    重複を取り除くには、同じ行かどうかを判定する必要があります。

    データベースは、その処理にソートやハッシュ処理などを使用する場合があります。

    そのため、重複排除には追加の処理コストが発生することがあります。

    ただし、重複排除の際に必ずソートが行われるわけではありません。

    実際の処理方法は、データベース製品や実行計画によって異なります。

    UNIONとUNION ALLの使い分け

    実務では、重複を取り除く必要がない場合、UNION ALL が有力な選択肢になります。

    ただし、常に UNION ALL が速いと断定できるわけではありません。

    重要なのは、必要な結果に合わせて演算子を選ぶことです。

    なお、集合演算の結果の並び順は、ORDER BY を指定しない限り保証されません。

    SQLBoltで特定の順番に表示されても、その順番が常に維持されるとは限らない点にも注意しましょう。


    9.冪等性(べきとうせい)を理解する

    集合演算には、数学的な性質があります。

    その一つが、冪等性です。

    冪等性とは、ある演算を同じ対象に繰り返しても、結果が変わらないという性質です。

    集合演算では、次の式が成り立ちます。

    UNIONの冪等性

    A ∪ A = A

    集合Aと集合Aを合わせても、結果はAのままです。

    SQLで考えると、

    SELECT building_name
    FROM buildings
    
    UNION
    
    SELECT building_name
    FROM buildings;

    同じデータを2回取得して UNION しても、重複が排除されるため、結果は変わりません。

    INTERSECTの冪等性

    A ∩ A = A

    集合Aと集合Aの共通部分を求めても、結果はAのままです。

    SELECT building_name
    FROM buildings
    
    INTERSECT
    
    SELECT building_name
    FROM buildings;

    こちらも結果はAと同じになります。

    EXCEPTは冪等ではない

    一方、差集合では、

    A − A = ∅

    となります。

    同じ集合から同じ集合を取り除くため、結果は空集合です。

    SELECT building_name
    FROM buildings
    
    EXCEPT
    
    SELECT building_name
    FROM buildings;

    今回のAは空集合ではないので、結果はAと一致しません。

    したがって、EXCEPTは一般に冪等性を持ちません。

    また、UNION ALL は重複を残すため、同じ結果を2回連結すると行数が増えます。

    そのため、UNION ALL も通常の意味では冪等ではありません。


    10.集合の相等性を調べる

    集合演算の知識を使うと、2つの集合が等しいかどうかも調べられます。

    ここで重要なのは、集合Aと集合Bが等しいとは、両方に含まれる要素が完全に一致していることです。

    差集合を使う方法

    次の2つが両方とも成り立つ場合、

    A − B = ∅

    B − A = ∅

    集合Aと集合Bは等しいと判断できます。

    つまり、Aにしかない要素も、Bにしかない要素も存在しないということです。

    今回のデータでは、

    • A − B:1w、2e

    • B − A:空集合

    となります。

    したがって、AとBは等しくありません。

    UNIONとINTERSECTを使う考え方

    集合には、次の関係もあります。

    A = B ⇔ A ∪ B = A ∩ B

    和集合と積集合が同じ集合になるのは、AとBが等しい場合です。

    これも、集合の相等性を考えるうえで役立つ性質です。

    なお、冪等性そのものと集合の相等性は別の概念です。

    冪等性は「同じ集合同士で演算したときの性質」、相等性は「2つの集合に含まれる要素が一致するか」という違いがあります。


    11.補足:除算という考え方

    集合演算に関連する知識として、**除算(関係除算)**にも触れておきます。

    関係除算は、例えば次のような条件を表すときに登場します。

    「指定されたすべての商品を購入した顧客を探す」

    「すべての必須研修を受講した社員を探す」

    このように、ある条件の集合をすべて満たしているかを調べる考え方です。

    一般的なSQLには、関係除算を直接表す標準的な専用演算子はありません。

    そのため、NOT EXISTS を組み合わせたり、集約処理を使用したりして表現します。

    これは、以前の講座で取り上げた「全称文」や「NOT EXISTSの二重否定」ともつながる考え方です。

    今回の第15回では詳しいSQLの実装には踏み込みませんが、集合演算の学習を通じて、こうした考え方にもつながることを覚えておきましょう。


    12.実務で使うときの注意点

    最後に、集合演算を実務で使用する際の基本的な注意点を整理します。

    SELECT文の列数を合わせる

    集合演算で組み合わせるSELECT文は、対応する列数が同じである必要があります。

    今回のSQLでは、どちらも建物名の1列を取得しているため、組み合わせられます。

    対応する列のデータ型に注意する

    対応する列のデータ型には、互換性が必要です。

    今回の building_name と building は、どちらも建物名を表す文字列なので、比較しやすい構成になっています。

    列名ではなく列の位置で対応する

    集合演算では、SELECT文同士の列が基本的に位置で対応します。

    例えば、2列を取得する場合は、1列目同士、2列目同士が対応します。

    列の意味がずれていると、意図しない結果になる可能性があります。

    NULLや重複の扱いを意識する

    集合演算では、通常の比較条件とは異なり、重複排除や共通行の判定においてNULL同士が同じ値として扱われる場合があります。

    また、UNION ALL では重複が残ります。

    そのため、単にSQLが実行できるだけでなく、どの行が残るべきなのかを意識することが大切です。


    まとめ

    今回は、SQLBolt Lesson 8のBuildingsとEmployeesを使い、集合演算を練習しました。

    重要なポイントは次のとおりです。

    • UNION は、2つの結果を合わせて重複を排除する

    • UNION ALL は、重複を残したまま結果を連結する

    • EXCEPT は、一方にだけ存在する行を取得する

    • INTERSECT は、両方に共通する行を取得する

    • 集合演算は、ベン図と照らし合わせると理解しやすい

    • 重複排除には処理コストが発生することがある

    • UNION と INTERSECT は冪等性を持つ

    • 差集合を使うと、集合の相等性を確認できる

    SQLの構文を覚えることも大切ですが、それ以上に、演算を実行するとどの行が残るのかをイメージできることが重要です。

    最初はベン図を描きながら確認し、慣れてきたら頭の中だけで結果を予想してみましょう。

    この練習を繰り返すことで、集合演算も少しずつ「息をするように書ける」ようになっていきます。


    講座のご案内

    SQLステップアップ講座|全16回の目次

    前回の記事

    第14回:自己結合を段階的に理解する(CROSS JOIN・結合条件・DISTINCT)

    次回の記事

    第16回:外部結合でEXCEPTを実現する(最終回)


     
     
    SQL初心者から、実務で使える中級者へ。SQLBoltを活用した16テーマの実践トレーニングと、製造業の業務データを題材にしたオリジナルSQL演習問題集(全20問)を公開中。SQLを「息をするように書ける」力を身につけることを目指します。

    あなたへのおすすめ