关于双重NOT EXISTS的一个撕烤

数据库原理复习专栏

• 已知关系：

  • employees(emp_no, first_name, last_name, hire_date, birth_date)
  • departments(dept_no, dept_name)
  • dept_emp(emp_no, dept_no, from_date, to_date)

  问：如何查找在所有部门都工作过的员工的编号和姓名？

• 事实上，窝们只需要从dept_emp表中得到某个员工工作过的部门数，检查其是否等于部门总数就可以了：

select emp_no, first_name, last_name from employees
where emp_no in (
  select emp_no from dept_emp group by emp_no
  having count(distinct dept_no)
    = (select count(*) from departments)
);

• 不过，如果一定要用NOT EXISTS来做呢？之前从MySQL的文档中发现了一个类似的，实验作业就直接照葫芦画瓢了，但一直没明白是肿么回事。。。今天迫于复习压力，终于相通。。。
• 可以根据NOT EXISTS的含义做转换，要求的问题便是“是否存在某员工，对他而言，不存在某个部门是他没工作过的”。然后根据下述步骤逐步求解。
• 从所有部门中，找出从没有人工作过的部门信息：

select * from departments D
where not exists (
  select * from dept_emp R
  where R.dept_no = D.dept_no
);

• 给定员工x，从所有部门中，找出x从未工作过的部门信息：

select * from departments D
where not exists (
  select * from dept_emp R
  where R.emp_no = x and R.dept_no = D.dept_no
);

• 上述的查询结果有两种可能：

  • 结果为空：不存在x没工作过的部门，也就是x同学在所有部门都工作过
  • 结果不为空：存在x没工作过的部门

  窝们需要的是第一种情况。对上述查询结果使用NOT EXISTS，可以让窝们获得一个布尔值，记为Z(x)。于是使用上面的语句，给定一个员工x，Z(x)便可以回答“是”(true)或者“否”(false)，为“是”(true)时便表示x在所有部门都工作过。由此，窝们得到一个非常简洁的查询，用来查找在所有部门都工作过的员工信息：

select * from employees E
where Z(当前从employees表取出的这个员工);

• 注意，在下述语句：

select * from
  employees natural join dept_emp
  where employees.first_name = 'Annie'
    and dept_emp.dept_no = 'd003';

中，where后可以直接用表名.xxx来指代当前迭代到的那一个元组。所以窝们的查询语句可以写成：

select * from employees E
where Z(E); --此E非彼E，where后出现的表名均指代该表中每次迭代到的一个具体元组

• 最后，根据窝们上面对Z(x)的设置，就有了：

SELECT emp_no, CONCAT(first_name, ' ', last_name) AS name FROM employees
WHERE NOT EXISTS (
  SELECT * FROM departments WHERE NOT EXISTS (
    SELECT * FROM dept_emp
    WHERE dept_emp.dept_no = departments.dept_no AND dept_emp.emp_no = employees.emp_no
  )
);

• 以上