【SQL】Except文を使って、マスタテーブルの全レコードを別のテーブルが保持するかを検証する

例えば、あるスキルセットを保持した社員を抽出するSQLを考えていきます。
テーブルは、「スキル一覧」と「社員」からなります。

「スキル一覧」はその名の通り、スキルセットを表します。
この一覧にあるスキルを全て保持する社員を探していきます。

「社員」テーブルは、その社員が保持しているスキルを表しています。

Exceptを使った例

このようなテーブル構造において、「スキル一覧」の全てのスキルを保持する社員を探すSQLを作成していきます。
以下はExcept文を使用した一例です。

全てのスキルを保持する社員を探すSQL

select distinct mem1.name
from p8_member mem1
where not exists (
  select sk.skill_name
  from p8_skill sk
  except
  select mem2.skill_name
  from p8_member mem2
  where mem1.name = mem2.name
);

not exists内のサブクエリを見てみてください。
この中で、スキル一覧と各社員のスキルセットの差集合を求めています。
スキル一覧 - 各社員のスキルセットですね。
ここで、その社員のスキルセットがスキル一覧を全て網羅していれば、差集合は空となります。
差集合が空であれば、それはnot existsの条件に合致しますので、その社員は全てのスキルを保持しているということになります。
あとは、対象のレコードをnameでdistinctして重複レコードを一つにまとめています。

一見何をやっているかわからないクエリに見えますが、ベン図を書いて考えてみると、そんなに難しいことをしているわけではないことがわかります。

別解

上記のSQLは集合論的な考え方で答えを求めています。
これを手続き的に考えると以下のようなクエリになります。
最初は、順番に考えていくこちらの方が理解しやすいかもしれません。

手続き的な考え方

select tmp2.name
from (
  select
    tmp.name, tmp.skill_name,
    -- ④一つでも1があれば習得済みとみなす
    max(is_laerned) is_laerned
  from(
    select
    mem.name, sk.skill_name,
    -- ②スキルを保持しているか否かのフラグ
    case when mem.skill_name = sk.skill_name then 1 else 0 end is_laerned
    from p8_member mem
    cross join p8_skill sk
    -- ①スキル一覧にあるスキルのみを対象とする
    where exists(
    select *
    from p8_skill sk2
    where sk2.skill_name = mem.skill_name
    )
  ) tmp
  -- ③名前、スキルでグルーピング
  group by
    tmp.name, tmp.skill_name
  ) tmp2
group by tmp2.name
-- ④すべてのスキルを習得している社員で絞り込み
having min(tmp2.is_laerned) = 1;