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

第6回:NOT EXISTSの二重否定による全称判定

    今回のテーマは、NOT EXISTSを使った全称判定です。

    例えば、次のような条件を考えてみましょう。

    「ある職種に所属する従業員が、全員、特定の条件を満たしている」

    このような「全員が条件を満たす」という判定は、SQLでどのように表現すればよいでしょうか。

    今回のポイントは、「すべての行が条件Pを満たす」を、「条件Pを満たさない行が一つも存在しない」と言い換えることです。

    この考え方を理解すると、NOT EXISTSを使って、集合全体に対する条件を表現できるようになります。

    今回の練習におすすめのSQLBolt

    今回使用するのは、SQLBolt Lesson 11:Queries with aggregates (Pt. 2) です。

    このLessonで使用する employees テーブルには、従業員の職種や勤続年数などが登録されています。

    今回は、このテーブルを使って「職種ごとに、全員が条件を満たしているか」を判定します。

    なお、SQLBoltのLesson 11自体が、今回紹介するNOT EXISTSによる全称判定を直接教える内容というわけではありません。

    Lesson 11のテーブル構成やデータが、今回のSQLパターンを練習するのに使いやすいため、おすすめしています。

    今回使用するテーブル

    使用するのは、employees テーブルです。

    画像

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

    • role:職種

    • years_employed:勤続年数

    今回は、職種(Role)ごとに従業員を一つの集合として考えます。

    そして、その集合に所属する全員が、ある条件を満たしているかどうかを調べます。

    ※画像はEmployeesテーブルの一部を表示したものです。実際のSQLはテーブル内のすべてのデータを対象に実行します。

    今回の練習問題

    問題:所属する従業員全員の勤続年数(years_employed)が3の倍数であるRoleについて、そのRoleに所属する従業員をすべて抽出してください。

    今回の条件Pは、次のとおりです。

    条件P:勤続年数が3の倍数である

    例えば、勤続年数が3年、6年、9年であれば条件Pを満たします。

    一方、2年、4年、7年などは条件Pを満たしません。

    ここで重要なのは、個々の従業員が条件Pを満たすかどうかだけではありません。

    同じRoleに所属する従業員が、全員条件Pを満たしていることが必要です。

    そして今回は、条件を満たしたRoleの名前だけではなく、そこに所属する従業員を明細行のまま抽出します。

    今回使用するSQL

    select a.*
    from employees a
    where not exists(
        select *
        from employees b
        where b.role = a.role  /* Role毎に分けて調べる */
          and b.years_employed % 3 <> 0  /* 条件Pの否定形 */
    )

    SQLBoltのエディターに貼り付けて、実行してみてください。

    実行結果

    画像

    今回の結果では、Managerに所属する3人が抽出されます。

    • Scott K.:勤続9年

    • Shirlee M.:勤続3年

    • Daria O.:勤続6年

    全員の勤続年数が3の倍数になっています。

    SQLの考え方を詳しく見てみましょう

    ① 「全員が条件Pを満たす」を言い換える

    ここが今回、最も大切なポイントです。

    「全員が条件Pを満たす」

    この条件を、そのままSQLにしようとすると、少し難しく感じるかもしれません。

    そこで、考え方を反対にしてみます。

    「条件Pを満たさない人が、一人も存在しない」

    実は、この2つは同じ意味になります。

    例えば、Managerの従業員が3人いて、勤続年数が9年、3年、6年だったとします。

    全員が3の倍数なので、条件Pを満たさない従業員は一人も存在しません。

    つまり、Managerは条件を満たす集合です。

    一方、Engineerには勤続4年や2年の従業員がいます。

    条件Pを満たさない従業員が存在するため、Engineerという集合全体は条件を満たしません。

    このように、全員が条件を満たしているかどうかを、条件に違反する人の存在有無で判定するのが、今回の考え方です。

    ② 条件Pの否定形を作る

    今回の条件Pは、

    「勤続年数が3の倍数である」

    です。

    SQLでは、次のように表現できます。

    years_employed % 3 = 0

    % は、割り算の余りを求める演算子です。

    3で割った余りが0なら、3の倍数ということになります。

    では、この条件の否定形はどうなるでしょうか。

    「勤続年数が3の倍数ではない」

    です。

    SQLでは、次のように表現できます。

    years_employed % 3 <> 0

    今回のNOT EXISTSでは、この条件Pの否定形を使います。

    ③ Roleごとに集合を分けて調べる

    今回のSQLで重要なのが、次の条件です。

    b.role = a.role

    これは、外側のクエリで現在判定している従業員と、同じRoleの従業員だけをサブクエリで調べるための条件です。

    例えば、外側の従業員がEngineerであれば、サブクエリではEngineerだけを調べます。

    外側の従業員がManagerであれば、サブクエリではManagerだけを調べます。

    つまり、Roleごとに集合を分けて、その集合の全員を調べているわけです。

    この部分は、第5回で練習した相関サブクエリの考え方ともつながっています。

    ④ NOT EXISTSで「違反者がいない」を判定する

    サブクエリでは、次の2つの条件を指定しています。

    • 外側の従業員と同じRoleである

    • 勤続年数が3の倍数ではない

    つまり、同じRoleの中から、条件Pに違反する従業員を探しているのです。

    そして、そのサブクエリ全体をNOT EXISTSで囲みます。

    NOT EXISTSは、サブクエリの結果が1行も存在しない場合に真になります。

    したがって、

    「同じRoleの中に、3の倍数ではない従業員が一人もいない」

    という条件を表現できます。

    これが、NOT EXISTSによる全称判定です。

    メインクエリの1行ごとの動きをイメージする

    第5回と同様に、今回も外側のクエリの行を一つずつ取り出して考えてみましょう。

    Engineerを判定する場合

    外側の従業員がEngineerの場合、サブクエリはEngineerの従業員を対象にします。

    Engineerには、勤続年数が4年や2年の従業員がいます。

    これらは3の倍数ではありません。

    つまり、サブクエリで条件に違反する従業員が見つかります。

    そのためNOT EXISTSは偽になり、Engineerの従業員は抽出されません。

    Artistを判定する場合

    Artistにも、勤続年数が2年や7年の従業員がいます。

    こちらも、3の倍数ではない従業員が存在します。

    そのため、Artistの従業員も抽出されません。

    Managerを判定する場合

    Managerの従業員の勤続年数は、9年、3年、6年です。

    いずれも3の倍数です。

    つまり、Managerの中には、条件Pに違反する従業員が一人も存在しません。

    そのためNOT EXISTSが真になり、Managerの従業員が抽出されます。

    外側の行に応じてRoleを切り替え、そのRoleに条件違反者が存在しないかを調べる。

    この動きを頭の中でイメージできるようになることが大切です。

    ※ここでの「1行ずつ判定する」という説明は、SQLの意味を理解するための考え方です。実際のデータベースが必ず行ごとにサブクエリを個別実行するとは限りません。

    GROUP BY・HAVINGとの違い

    今回のSQLには、もう一つ重要なポイントがあります。

    それは、条件を満たすRoleの従業員を、明細行のまま抽出できるということです。

    GROUP BYとHAVINGを使っても、「全員が条件Pを満たすRole」を求めることはできます。

    しかし、Roleごとに集約した結果から、そのまま各従業員の氏名や勤続年数などを自由に表示することはできません。

    従業員の明細情報まで取り出すには、別のクエリと結合するなどの工夫が必要です。

    一方、今回のNOT EXISTSでは、外側のクエリがEmployeesテーブルの各行を扱っています。

    そのため、条件を満たしたRoleに所属する従業員について、a.* で各列をそのまま取り出せます。

    集合全体に対する条件で判定しながら、結果は明細行として取得できる。

    これは、実務でも役立つ特徴です。

    実務ではどんな場面で使う?

    例えば、製造業の業務データでは、次のような場面が考えられます。

    • すべての明細が検収済みになっている発注について、その明細を一覧表示する。

    • すべての工程が完了している製造指示について、工程の明細を抽出する。

    • すべての対象品目が条件を満たしている取引先について、その品目を一覧表示する。

    いずれも、「グループ内の全件が条件を満たすか」を判定し、そのグループの明細を取得するという考え方です。

    こうしたSQLパターンは、実務でも使う場面があります。

    今回のまとめ

    今回覚えてほしいポイントは、次の3つです。

    ① 「すべての行が条件Pを満たす」は、「条件Pを満たさない行が一つも存在しない」と言い換えられる。

    この考え方が、NOT EXISTSによる全称判定の基本です。

    ② カテゴリーごとに集合を分けて調べる。

    今回の b.role = a.role のように、外側と内側のクエリを関連付けることで、Roleごとの全称判定ができます。

    ③ 条件を満たした集合のメンバーを、明細行のまま抽出できる。

    GROUP BYで集約した結果とは異なり、NOT EXISTSでは外側の各行をそのまま取得できます。

    最初は、NOT EXISTSの中に否定条件を書くことに違和感があるかもしれません。

    しかし、**「全員が満たす」→「違反者が一人もいない」**という変換に慣れると、自然にSQLを書けるようになります。

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

    一つひとつのSQLパターンを、自分のものにしていきましょう。


    講座のご案内・記事一覧

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

    前回の記事

    第5回:相関サブクエリ

    次回の記事

    第7回:CASE式を使った行から列への展開

    次回も、SQLBoltのテーブルを使いながら、実際にSQLを書いて練習していきます。

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

    あなたへのおすすめ