2021-11-29 解一个SQL关系除法

最近学sql遇到一个坑，即所谓的关系除法，比较难以理解，在这里记录一下。

有这样两个表：

table: skills
 skill
 --------
 Oracle
 UNIX
 Java
 (3 ROWS)
 
 table: EmpSkills
  emp   | skill  
--------+--------
 相田   | Oracle
 相田   | UNIX
 相田   | Java
 相田   | C#
 神崎   | Oracle
 神崎   | UNIX
 神崎   | Java
 平井   | UNIX
 平井   | Oracle
 平井   | PHP
 平井   | Perl
 平井   | C++
 若田部 | Perl
 渡来   | Oracle
 (14 rows)

要求：选出满足表skills中所有技能的员工.

SQL实现:

SELECT
    DISTINCT emp
FROM
    EmpSkills ES1
WHERE
    NOT EXISTS (
        SELECT
            skill
        FROM
            Skills
        EXCEPT
        SELECT
            skill
        FROM
            EmpSkills ES2
        WHERE
            ES1.emp = ES2.emp
    );

第一次看，完全不知所云，于是进行拆解：

选出所有员工，去除重复

SELECT
    DISTINCT emp
FROM
    EmpSkills ES1

结果：渡来 若田部 平井 神崎 相田

把where子句拆出来，先去掉not exists语义：

同时为了方便看懂，把es1.emp换成一个真实值：

选择所有需要满足的skill

        SELECT
            skill
        FROM
            Skills

结果：Oracle UNIX Java

对结果取差集， 也就是在结果 ( Oracle UNIX Java ) 中存在，但是在如下select语句中不存在

        EXCEPT --取差集语义
        SELECT
            skill
        FROM
            EmpSkills ES2
        WHERE
            '相田' = ES2.emp

当员工为 ‘相田’ 时，从员工表empskills中选出的skill是：

 emp  | skill  
------+--------
 相田 | Oracle
 相田 | UNIX
 相田 | Java
 相田 | C#
(4 rows)

没有在skills表中有而此员工没有的技能。

也就是说’相田’满足not exists语义，于是就被选了出来。

重新看一下实现，现在就比较清楚在做什么了：

翻译一下：

1. 选出所有员工从员工表（去重复： 渡来 若田部 平井 神崎 相田）
2. 筛选条件：下面第3条不能查询出任何结果
3. 选出技能表中有，而某个员工（例如 ‘渡来’ ）没有的技能（ UNIX Java ）。
4. 其实就是 “不能不包括” = “必须全包括” 的语义：[ not exists ( xxx except xxx where xxx = xxx) ]

SELECT
    DISTINCT emp
FROM
    EmpSkills ES1
WHERE
    NOT EXISTS (
        SELECT
            skill
        FROM
            Skills
        EXCEPT
        SELECT
            skill
        FROM
            EmpSkills ES2
        WHERE
            ES1.emp = ES2.emp
    );

以上就是选出所有满足skills表中技能的所有员工。

也就是 不能不包括 skill表中所有技能的所有员工。

参考书: SQL基础教程（第2版）作者：[日]MICK译者：孙淼，罗勇 出版社：人民邮电出版社出版时间：2017-06ISBN：9787115455024 第七章 7-2 联结（以列为单位对表进行联结） 关系除法