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

第16回:外部結合でEXCEPTを実現する

    はじめに

    SQLステップアップ講座も、今回でいよいよ最終回となりました。

    前回の第15回では、UNION・UNION ALL・EXCEPT・INTERSECTという4つの集合演算について学びました。

    今回は、その中の**EXCEPT(差集合)**に注目します。

    EXCEPTは、2つの集合を比較して、左側にしか存在しないデータを抽出する演算です。

    実は、同じ目的の抽出処理を、LEFT JOIN(左外部結合)とIS NULLを組み合わせても実現できます。

    今回は、両方のSQLを実際に書いて比較しながら、なぜ外部結合で差集合を表現できるのかを詳しく確認していきます。

    今回も、SQLの書き方を暗記するのではなく、処理の仕組みを理解して、自然に書けるようになることを目指しましょう。


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

    今回も、SQLBoltのLesson 8に登場するテーブル構成とデータを使って演習します。

    SQLBolt Lesson 8

    なお、Lesson 8自体が今回のテーマを学ぶためのレッスンというわけではありません。

    今回は、Lesson 8に用意されているBuildingsテーブルとEmployeesテーブルが、外部結合や差集合の演習に使いやすいため、学習環境として利用します。

    Buildingsテーブル

    建物の情報が登録されているテーブルです。

    画像

    Employeesテーブル

    従業員の情報が登録されているテーブルです。

    画像

    ※Employeesテーブルの画像は、全15行のうち一部を抜粋したものです。

    今回注目するのは、次の2つの列です。

    • Buildingsテーブル:building_name

    • Employeesテーブル:building

    この2つの列を比較して、建物としては登録されているものの、従業員が誰も所属していない建物を探します。


    2.まずはEXCEPTで書いてみる

    最初に、前回学んだEXCEPTを使ってみましょう。

    SELECT building_name
    FROM buildings
    
    EXCEPT
    
    SELECT building
    FROM employees;

    実行結果

    画像

    取得できた建物は、次の2件です。

    • 1w

    • 2e

    EXCEPTは、上側のSELECT文で取得した集合から、下側のSELECT文で取得した集合に存在するデータを除外します。

    今回の場合は、

    • Buildingsに存在する建物:1e、1w、2e、2w

    • Employeesに所属先として登録されている建物:1e、2w

    となるため、1wと2eが残ります。

    つまり、従業員が所属していない建物だけを取得できたということです。


    3.LEFT JOINで書き換える

    それでは、EXCEPTを使わずに同じ結果を取得してみましょう。

    今回使用するのは、LEFT JOINとIS NULLの組み合わせです。

    SELECT DISTINCT
        a.building_name
    FROM buildings a
    LEFT JOIN employees b
        ON a.building_name = b.building
    WHERE b.building IS NULL;

    実行結果

    画像

    結果は、先ほどのEXCEPTと同じです。

    • 1w

    • 2e

    では、なぜこのSQLで同じ結果を取得できるのでしょうか。

    重要なのは、LEFT JOINの結合結果をイメージすることです。


    4.LEFT JOINの結合途中を確認する

    まずは、WHERE条件を取り除いて、結合結果を確認してみましょう。

    SELECT
        a.building_name,
        b.name,
        b.building
    FROM buildings a
    LEFT JOIN employees b
        ON a.building_name = b.building
    ORDER BY a.building_name;

    実行結果

    画像

    この結果を見ると、従業員が所属している建物にはEmployeesテーブルの情報が結合されています。

    一方、従業員が所属していない1wと2eでは、Employeesテーブル側の列がNULLになっています。

    この違いが、今回のSQLを理解するための重要なポイントです。


    5.なぜLEFT JOINとIS NULLで抽出できるのか?

    今回のSQLで重要なのは、LEFT JOINの特徴です。

    LEFT JOINは、左側のテーブルに存在するデータをすべて残しながら、右側のテーブルを結合します。

    今回の場合、左側がBuildingsテーブル、右側がEmployeesテーブルです。

    建物名が一致する従業員が存在すれば、その従業員の情報が結合されます。

    一方、一致する従業員が存在しない場合でも、Buildingsテーブルの行は削除されません。

    その代わり、Employeesテーブル側の列にはNULLが入ります。

    今回のデータでは、次のようになります。

    • 1e:従業員が存在するため、Employees側に値が入る

    • 1w:従業員が存在しないため、Employees側がNULLになる

    • 2e:従業員が存在しないため、Employees側がNULLになる

    • 2w:従業員が存在するため、Employees側に値が入る

    ここで、次の条件を指定します。

    WHERE b.building IS NULL

    すると、Employees側の建物名がNULLになっている行だけが残ります。

    つまり、**「Buildingsには存在するが、Employeesには対応するデータが存在しない建物」**を抽出できるのです。

    このように、LEFT JOINとIS NULLを組み合わせることで、EXCEPTと同じ目的の抽出処理を実現できます。

    ただし、両者はNULLや重複行の扱いが常に同じとは限りません。その違いについては、次に確認します。


    6.EXCEPTとLEFT JOINの違い・注意点

    ここまで、EXCEPTとLEFT JOIN+IS NULLを使って、同じ結果を取得できることを確認しました。

    しかし、両者は常に同じ結果になるわけではありません。

    特に注意したいのが、重複行とNULLの扱いです。

    ① 重複行の扱い

    EXCEPTは、結果から重複行を除去します。

    一方、LEFT JOIN+IS NULLでは、重複行がそのまま残る場合があります。

    例えば、左側のテーブルに同じ建物名が複数登録されていて、その建物に所属する従業員が存在しなかった場合、LEFT JOINでは同じ建物名が複数行返されます。

    そのため、EXCEPTと同じように重複を除去したい場合は、SELECT DISTINCTを指定します。

    ② NULLの扱い

    もう一つの注意点は、NULLです。

    EXCEPTでは、集合演算による比較において、両側のNULLを同じ値として扱います。

    一方、LEFT JOINの結合条件で使用する通常の等価比較(=)では、NULL同士は一致しません。

    例えば、両方のテーブルに建物名がNULLの行が存在する場合を考えます。

    EXCEPTでは、そのNULLは差集合の結果から除外されます。

    しかし、LEFT JOINで次の条件を指定した場合、

    ON a.building_name = b.building

    NULL同士は結合されません。

    そのため、LEFT JOIN+IS NULLでは、左側のNULL行が結果に残る場合があります。

    つまり、NULLを含むデータでは、EXCEPTとLEFT JOIN+IS NULLの結果が異なることがあるのです。

    ③ 実務ではどう使い分ける?

    今回のように、建物名がNULLにならないことが保証されている場合は、LEFT JOIN+IS NULLでEXCEPTと同じ目的の抽出ができます。

    ただし、実務では次の点を確認することが大切です。

    • 重複行を除去する必要があるか

    • 比較対象の列にNULLが含まれる可能性があるか

    • 複数列を比較する必要があるか

    また、SQLの実行性能はデータ量やインデックス、実行計画によって変わるため、どちらが必ず速いとは限りません。

    SQLの書き方だけでなく、データの性質を理解して使い分けることが重要です。


    7.実務での活用例

    LEFT JOINとIS NULLの組み合わせは、実務でもよく使われるSQLの書き方です。

    特に、**「あるテーブルには存在するが、別のテーブルには存在しないデータ」**を探したい場面で役立ちます。

    例えば、製造業の業務システムでは、次のようなケースが考えられます。

    ① 発注済みだが、まだ入荷していないデータを探す

    発注テーブルには登録されているものの、入荷テーブルには対応するデータが存在しない発注を抽出します。

    SELECT
        a.order_no
    FROM orders a
    LEFT JOIN receipts b
        ON a.order_no = b.order_no
    WHERE b.order_no IS NULL;

    このSQLでは、発注番号を使って発注データと入荷データを結合しています。

    入荷データが存在しない発注だけが抽出されるため、未入荷の発注を確認できます。

    ただし、分納がある場合は、一部入荷済みの発注は抽出されません。完全な未入荷と一部未入荷を区別するには、数量なども確認する必要があります。

    ② マスタに登録されているが、実績が存在しないデータを探す

    例えば、社員マスタには登録されているものの、特定期間の作業実績が存在しない社員を抽出するケースです。

    ③ 登録漏れやデータの不整合を確認する

    あるシステムには存在するデータが、連携先のシステムには登録されていない場合、その差分を確認するためにも利用できます。

    このように、LEFT JOINとIS NULLの組み合わせは、未登録・未処理・未実績などのデータを探す際に役立ちます。

    重要なのは、SQLを丸暗記することではありません。

    「左側のデータを残し、右側に一致するデータがないものを探す」

    この考え方を理解しておけば、テーブル名や項目名が変わっても応用できます。

    こうしたSQLのパターンを繰り返し練習し、息をするように書ける状態を目指しましょう。


    8.まとめ

    今回は、EXCEPTで実現できる差集合の抽出を、LEFT JOINとIS NULLを組み合わせて表現する方法を学びました。

    今回のポイントを振り返りましょう。

    • EXCEPT:2つの集合を比較し、左側にしか存在しないデータを抽出する。

    • LEFT JOIN:左側のテーブルのデータを残しながら、右側のテーブルを結合する。

    • IS NULL:右側に一致するデータが存在しなかった行を抽出する。

    • DISTINCT:必要に応じて、結果の重複行を除去する。

    特に重要なのは、LEFT JOINの結合結果をイメージできるようになることです。

    「右側に一致するデータがなければ、右側の列がNULLになる」

    この仕組みを理解していれば、未登録データや未処理データの抽出にも応用できます。

    ただし、EXCEPTとLEFT JOIN+IS NULLは、NULLや重複行の扱いによって結果が異なる場合があります。

    SQLの構文だけでなく、データの性質まで意識することが大切です。


    SQLステップアップ講座・全16回を終えて

    今回で、SQLステップアップ講座は最終回となります。

    この講座では、SQLの基本構文を一通り学んだ方が、もう一段階ステップアップするために、さまざまなSQLの書き方や考え方を紹介してきました。

    CASE、EXISTS、NOT EXISTS、GROUP BY、サブクエリ、自己結合、集合演算など、扱ってきたテーマはさまざまです。

    しかし、どのテーマにも共通する大切な考え方があります。

    それは、SQLを単に暗記するのではなく、「なぜその結果になるのか」を理解することです。

    最初は、SQLを見ながら考えたり、何度も書き直したりすることもあると思います。

    それでも、同じパターンを繰り返し練習していくうちに、少しずつ自然に書けるようになっていきます。

    例えば、

    • データの存在を確認したいなら、EXISTS

    • 存在しないデータを探したいなら、NOT EXISTS

    • 条件によって結果を分けたいなら、CASE

    • 集計結果に条件を付けたいなら、HAVING

    • 集合の差を求めたいなら、EXCEPTやLEFT JOIN+IS NULL

    といったように、目的に応じたSQLの書き方が自然に思い浮かぶようになることを目指してきました。

    この講座で紹介したSQLを、一度読んで終わりにするのではなく、ぜひ繰り返し書いてみてください。

    テーブル名や条件を変えてみるだけでも、理解はさらに深まります。

    そして、いつかSQLを書くときに、構文を一つひとつ思い出すのではなく、目的に合ったSQLが自然と手から出てくる。

    「息をするように書ける」

    それが、このSQLステップアップ講座で目指してきた姿です。

    全16回、最後までお読みいただき、ありがとうございました。

    これからも、日々のSQL学習や実務の中で、この講座が少しでもお役に立てればうれしいです。


    講座のご案内

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

    前回の記事

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


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

    あなたへのおすすめ