mySQL中exists关键字的详细用法

今天总结的关于exists的作用和用法，借鉴了一些他人的想法，并进行了一些补充，希望对你们有所帮助

引言

exists可以算得上是mySQL的一个关键字，在很多sql语句中经常可以用到，为了更好地理解exists关键字的用法，下面从一个简单的例子引入，下面是一个sql语句例子

select * from studentwhere not EXISTS (select 1 from course where not EXISTS(select 1 from sc        where SC.S_id=student.S_id and SC.C_id=course.C_id))

前置知识

要想理解exists的作用，你要知道什么是相关子查询和不相关子查询

数据准备

-- 课程信息DROP TABLE IF EXISTS `student`;CREATE TABLE `course`  (  `C_id` int NOT NULL,  `C_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,  PRIMARY KEY (`C_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;INSERT INTO `course` VALUES (1, '语文');INSERT INTO `course` VALUES (2, '英语');INSERT INTO `course` VALUES (3, '数学');-- 选修记录DROP TABLE IF EXISTS `sc`;CREATE TABLE `sc`  (  `S_id` int NOT NULL,  `C_id` int NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;INSERT INTO `sc` VALUES (1, 1);INSERT INTO `sc` VALUES (1, 2);INSERT INTO `sc` VALUES (1, 3);INSERT INTO `sc` VALUES (2, 2);-- 学生信息DROP TABLE IF EXISTS `student`;CREATE TABLE `student`  (  `S_id` int NOT NULL,  `s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,  PRIMARY KEY (`S_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;INSERT INTO `student` VALUES (1, '张三');INSERT INTO `student` VALUES (2, '李四');INSERT INTO `student` VALUES (3, '王五');

具体图如下

基本作用

EXISTS 用于在主查询中执行条件判断，以确定是否存在符合子查询条件的行。它返回布尔值（TRUE 或 FALSE），表示子查询是否至少返回一行结果。

例如：

SELECT s_id, s_nameFROM studentWHERE EXISTS (    SELECT 1    FROM sc    WHERE sc.s_id = student.s_id);

执行步骤是：

• 从student表中取第一条数据带入到子查询中

• 判断在sc表中至少存在一条数据满足条件sc.s_id = student.s_id

• 存在

  • 返回student表中s_id和s_name字段

  • 不存在，例如：

    SELECT s_id, s_name FROM student WHERE EXISTS ( SELECT 1 FROM sc WHERE sc.s_id = 4 );

    没有任何输出

    所以上面sql语句输出结果为：

    所以上面sql语句的意思是

    对于student表中的每一条数据来说，遍历sc表满的数据足条件sc.s_id = student.s_id的记录的s_id，s_name

    也就是说查找有选修记录的学生信息

    exists嵌套

    了解基本作用后，就可以尝试了解最开始的问题

    select * from studentwhere not EXISTS (select 1 from course where not EXISTS(select 1 from sc        where SC.S_id=student.S_id and SC.C_id=course.C_id))

    可以直观看出，上面有两层嵌套

    这是第一层：

    select 1 from course where not exists (select 1 from course where ...)

    这是第二层

    select 1 from sc where SC.S_id=student.S_id and SC.C_id=course.C_id)

    不要慌，我们一步一步分析

    • 从student表中取一条数据，放到第一层exists嵌套中

    • 然后判断是否不存在满足第一层嵌套exists的条件where not exists (select 1 from course where ...)

      • 这是一个exists的嵌套语句，同理，再在course表中取一条数据，然后和student表中的第一条数据，放到第二层嵌套中

        select 1 from sc where SC.S_id=student.S_id and SC.C_id=course.C_id)

        可以发现已经没有嵌套了，来判断是否不存在一条数据满足上面条件

      • 可以发现满足条件，也就是exists，即存在选修记录，返回false

      • 接着带入course表中第二条数据，也存在，返回false,

      • 接着带入course表中第三条数据，也存在，返回false,

      • 此时course表中没有其他数据，也就是说不存在他没有选修过的课程，返回true,输出第一条学生信息

      • 同理，从student表中取第二条数据，放到第一层exists嵌套中，判断是否满足

        • …………
        • 带入course表中第一条数据，不存在选修记录，返回true,
        • 此时course表存在他没有选修过的课程，返回false,不输出

        • 同理，从student表中取第三条数据，放到第一层exists嵌套中，判断是否满足

          • …………
          • 带入course表中第一条数据，也不存在他没选修过的课程，返回true，对于第一个not exists而言，只有false才输出
          • 此时course表存在他没有选修过的课程，返回false,不输出

            对于student表中数据而言，不存在选修记录中不存在的课程的学生信息

            输出结果为

            最后总结发现，上面sql句子的意思是

            所有课程都选修的学生

            也就是说查找选修了所有课程的学生信息。

            类似的还有以下内容

            select student.s_id, student.s_namefrom studentwhere EXISTS (select 1 from course where not EXISTS(select 1 from sc        where SC.S_id=student.S_id and SC.C_id=course.C_id))

            所有课程还没对应的学生

            答案：

            select student.s_id, student.s_namefrom studentwhere not EXISTS (select 1 from course where EXISTS(select 1 from sc        where SC.S_id=student.S_id and SC.C_id=course.C_id))

            课程都不对应的学生

            答案：3

            select * from studentwhere EXISTS (select 1 from course where EXISTS(select 1 from sc        where SC.S_id=student.S_id and SC.C_id=course.C_id))

            课程对应的学生

            答案：1，2

            参考链接

            https://zhuanlan.zhihu.com/p/20005249

            https://cloud.tencent.com/developer/article/2146774