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

第3回:NOT EXISTSを使った非存在判定

    SQLを「息をするように書ける」ようになるための、実践トレーニング第3回です。

    今回は、NOT EXISTSを使って、関連するデータが存在しないレコードを抽出するSQLパターンを練習します。

    NOT EXISTSは、構文だけを見ると比較的シンプルです。しかし、実際の業務では非常によく使うため、ぜひ身につけておきたいSQLパターンの一つです。

    また、EXISTSは通常の比較条件とは少し異なる考え方を持っています。

    この考え方を理解して使いこなせるようになることは、SQL初級から中級へステップアップするうえで重要なポイントだと思います。

    1. おすすめの練習場所:SQLBolt Lesson 8

    今回の練習には、SQLBoltのLesson 8を使用します。

    Lesson 8には、Buildings(建物)とEmployees(従業員)の2つのテーブルが用意されています。

    この2つのテーブルは、「建物は登録されているけれど、その建物に所属する従業員がいない」 という状態を調べる練習に適しています。

    なお、Lesson 8自体がNOT EXISTSを学ぶためのレッスンというわけではありません。今回は、そこに用意されたテーブル構成とデータを利用して、オリジナルのSQLトレーニングを行います。

    使用するテーブル

    Buildings

    画像

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

    Employees

    画像

    従業員の職種、名前、所属する建物、勤続年数を管理するテーブルです。

    2. 今回の練習問題

    Buildingsテーブルに登録されている建物のうち、Employeesテーブルに所属する従業員が1人も存在しない建物を抽出してください。

    出力する項目は、建物名(building_name)のみとします。

    ポイントは、Buildingsテーブルの各建物について、Employeesテーブルに該当する従業員が存在するかどうかを調べることです。

    3. 解答SQL

    select a.building_name
    from buildings a
    where not exists(select *
                     from employees b
                     where b.building=a.building_name
                     )

    実際にSQLを動かしてみましょう

    SQLBolt Lesson 8を開き、上記のSQLをコピーして、画面内のSQL入力欄に貼り付けて実行してみてください。

    https://sqlbolt.com/lesson/select_queries_with_nulls

    実際に動かして結果を確認することが、SQLを自然に書けるようになるための大切な練習です。

    4. 実行結果

    画像

    抽出される建物は、次の2件です。

    • 1w

    • 2e

    どちらもBuildingsテーブルには登録されていますが、Employeesテーブルには該当する建物に所属する従業員が存在しません。

    つまり、今回のSQLは正しく**「従業員が存在しない建物」**を抽出できています。

    5. SQLの考え方

    ① EXISTSは「存在するかどうか」を判定する

    EXISTSは、サブクエリが返す結果に行が存在するかどうかを判定する述語です。

    例えば、次の部分を見てください。

    exists(
        select *
        from employees b
        where b.building=a.building_name
    )

    これは、外側のBuildingsテーブルの建物名と一致する従業員が、Employeesテーブルに存在するかどうかを判定しています。

    該当する従業員が1件でも存在すればTRUE、1件も存在しなければFALSEとなります。

    ここで重要なのは、何件あるかを数えているわけではないということです。

    1件でも存在するか。それとも1件も存在しないか。

    EXISTSが調べているのは、その一点です。

    ② EXISTSは集合を引数に取る特殊な述語

    SQLには、例えば次のような条件があります。

    where years_employed > 3

    これは、勤続年数という値に対する比較です。

    一方、EXISTSはサブクエリによって得られる結果の集合を対象として、その中に行が存在するかどうかを判定します。

    つまり、個々の値を比較するのではなく、条件に合う行の集合が空かどうかを調べるという、一段階異なる考え方です。

    EXISTSは、論理学でいう「存在量化」に対応する考え方をSQLで表現するものです。

    初めは少し抽象的に感じるかもしれません。

    しかし、この考え方に慣れると、SQLで表現できる条件の幅が大きく広がります。

    ③ NOT EXISTSは存在判定を反転させる

    今回使用しているのは、EXISTSではなくNOT EXISTSです。

    where not exists(
        select *
        from employees b
        where b.building=a.building_name
    )

    EXISTSが「存在する場合にTRUE」となるのに対して、NOT EXISTSは「存在しない場合にTRUE」となります。

    今回の例では、

    • 建物1e:従業員が存在するため、抽出しない

    • 建物1w:従業員が存在しないため、抽出する

    • 建物2e:従業員が存在しないため、抽出する

    • 建物2w:従業員が存在するため、抽出しない

    という判定になります。

    その結果、1wと2eが抽出されるわけです。

    ④ 外側のテーブルと内側のテーブルの関係

    今回のSQLで、特に注目してほしいのが次の条件です。

    where b.building=a.building_name

    ここでは、内側のサブクエリから、外側のBuildingsテーブルの列を参照しています。

    このような書き方を相関サブクエリと呼びます。

    今回のSQLは、考え方として次のように読むことができます。

    「Buildingsテーブルの各建物について、その建物に所属する従業員がEmployeesテーブルに1人も存在しなければ、その建物名を出力する」

    この日本語の意味とSQLの構造を結びつけて理解することが大切です。

    ⑤ SELECT * の意味

    EXISTSの中では、次のように記述しています。

    select *

    EXISTSは、サブクエリから返される行が存在するかどうかを判定するため、通常はどの列をSELECTするかが判定結果に影響しません。

    そのため、今回のようにSELECT * と記述できます。

    6. このSQLパターンの活用場面

    NOT EXISTSは、実務でも頻繁に登場します。

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

    • 未発注の商品を抽出する:商品マスタには存在するが、発注実績がない商品

    • 未入荷の発注データを抽出する:発注情報はあるが、対応する入荷実績が存在しない発注

    • 売上実績のない顧客を抽出する:顧客マスタには存在するが、売上データがない顧客

    • 処理漏れを調べる:対象データは存在するが、対応する処理履歴が存在しないレコード

    このように、**「Aには存在するが、Bには存在しない」**という条件は、業務システムのデータ抽出で非常によく使います。

    NOT EXISTSを自然に書けるようになると、こうした抽出条件をSQLで表現しやすくなります。

    7. まとめ

    今回のポイントは、次の3つです。

    • EXISTSは、サブクエリの結果に行が存在するかどうかを判定する。

    • NOT EXISTSは、条件に合う行が1件も存在しない場合にTRUEとなる。

    • 相関サブクエリを使うことで、外側の各レコードに対応するデータの存在・非存在を判定できる。

    NOT EXISTSは、一見すると初級寄りのテーマに思えるかもしれません。

    しかし、存在するか、存在しないかを集合の視点で考えられることは、SQLを使いこなすうえで非常に重要です。

    まずは今回のSQLを、何も見ずに自然に書けるようになるまで繰り返してみてください。


    講座のご案内・記事一覧

    SQLステップアップ講座|講座のご案内・記事一覧

    前回の学習はこちら

    第2回:CASE式をGROUP BYで使う

    次回の記事

    第4回:CASE式を使った特性関数


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

    あなたへのおすすめ