MySQL的NOT EXISTS遭遇战

浅尝一下NOT EXISTS

最近老婆在看视频学习MySQL，然后碰到了这样一道习题：有三个表，分别记录学生、课程，以及学生选修了什么课程的信息，问如何用NOT EXISTS找出选修了所有课程的学生。

为了避免想破脑袋编造一些尴尬的学生姓名和课程名，我简化了一下习题中的表的结构，只留下它们的ID列。建表语句如下

-- 学生表
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 课程表
CREATE TABLE `course` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

-- 选修关系
CREATE TABLE `elective` (
  `student_id` INT NOT NULL,
  `course_id` INT NOT NULL,
  FOREIGN KEY (`student_id`) REFERENCES `student`(`id`),
  FOREIGN KEY (`course_id`) REFERENCES `course`(`id`)
);

还需要给它们塞入一些示例数据

INSERT INTO `student` (`id`) VALUES (1), (2), (3), (4), (5);
INSERT INTO `course` (`id`) VALUES (1), (2);
INSERT INTO `elective` (`course_id`, `student_id`) VALUES (1, 1), (2, 1), (1, 2), (2, 3), (2, 5), (1, 5);

显然，只有id列的值为1和5的学生是选修了全部课程的。用NOT EXISTS写出来的SQL语句如下

SELECT * 
FROM   `student` 
WHERE  NOT EXISTS (SELECT * 
                   FROM   `course` 
                   WHERE  NOT EXISTS (SELECT * 
                                      FROM   `elective` 
                                      WHERE  `student`.`id` = 
                                             `elective`.`student_id` 
                                             AND `course`.`id` = 
                                     `elective`.`course_id`)); 

在DBEaver中运行后的结果为

正确地找出了两个选修了所有课程的学生的id。

如何理解双重NOT EXISTS

当第一次被请教这道习题的时候，我其实并不能理解NOT EXISTS的含义。直到后来去看EXISTS的文档，才顿悟了上面的SQL。

我的理解方法是将双重NOT EXISTS转换为三层循环。以上面的SQL为例，转述为人话就是：找出student表中所有的、没有任何一门course表中的课程是没有选修的、的学生——双重的 没有。

转换为三层循环大概长这样

for (const student of students) {
  // 是否存在学生未选修的课程
  let existSuchCourse = false;
  for (const course of courses) {
    let existSuchElective = false;
    for (const elective of electives) {
      if (elective.student_id === student.id && elective.course_id === course.id) {
        existSuchElective = true;
        break;
      }
    }
    // 如果遍历完elective表的记录后，existSuchElective仍然为false，说明的确有一门课程是没有选修记录的
    // 那么便意味着“存在至少一门课程，使得当前被遍历的学生与该课程没有选修关系”。
    if (!existSuchElective) {
      existSuchCourse = true;
      break;
    }
  }
  // 如果遍历完一圈后确实没有找到“未选修”的课程，说明这名学生全都选修了
  if (!existSuchCourse) {
    console.log(student);
  }
}

NOT EXISTS的本质

即使不强行理解，也可以让MySQL明确告知双重NOT EXISTS是怎么运作的。用EXPLAIN解释上面的SQL的结果如下图所示

MySQL的EXPLAIN命令的文档中说明了如何解读执行计划

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. This means that MySQL reads a row from the first table, then finds a matching row in the second table, and then in the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

以上面的EXPLAIN为例，MySQL从student表中读出一行，再从course表中读取一行，最后从elective表中读取一行，然后看看WHERE子句是否能够被满足。如果可以，就输出从student表中读出来的这行数据。上图第2和第3行的select_type都是DEPENDENT SUBQUERY，表示它们依赖于“外层”的查询上下文——elective的WHERE子句依赖于student和course中读出来的行。

似乎和方才的三重循环有异曲同工之妙呢。

后记

像NOT EXISTS这么“高阶”的功能我从未在业务代码中读过和使用过——别说NOT EXISTS，就算是EXISTS也是从未有之，甚至连子查询也极少。毕竟“正经的互联网公司”只是把MySQL当妹妹当一个具备复杂查询查询功能的key-value数据库来使用（笑

比起双重NOT EXISTS，我更可能凭直觉写出基于子查询的解决方法

SELECT * 
FROM   `student` 
WHERE  `id` IN (SELECT `student_id` 
                FROM   `elective` 
                GROUP  BY `student_id` 
                HAVING( Count(0) ) = 2); 

我甚至觉得会有人把数据库里的行读进内存然后用应用层代码来找出选修了全部课程的学生！

全文完。