mysql双层not exists查询执行流程

一、单个EXISTS、NOT EXISTS用法

DROP TABLE IF EXISTS `t_staff`;
CREATE TABLE `t_staff` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人员表';

DROP TABLE IF EXISTS `t_major`;
CREATE TABLE `t_major` (
  `id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='技能表';

INSERT INTO `t_staff` VALUES ('1', '张辽');
INSERT INTO `t_staff` VALUES ('2', '赵云');
INSERT INTO `t_staff` VALUES ('3', '夏侯渊');

INSERT INTO `t_major` VALUES ('1', '2', '大鹏展翅');
INSERT INTO `t_major` VALUES ('2', '2', '无敌风火轮');
INSERT INTO `t_major` VALUES ('3', '3', '横扫千军');

EXISTS内层查询【非空】外层的where返回【真值】；内层查询【为空】外层的where返回【假值】
NOT EXISTS内层查询【非空】外层的where返回【假值】；内层查询【为空】外层的where返回【真值】

SELECT * FROM t_student_course WHERE TRUE; -- 能查出数据
SELECT * FROM t_student_course WHERE FALSE; -- 不能查出数据

1、查询至少有一个技能的人员信息（in、EXISTS）

SELECT * FROM t_staff where id in (SELECT staff_id FROM t_major);
SELECT * FROM t_staff where EXISTS (
    SELECT 1 from t_major where t_staff.id = t_major.staff_id
);

-- 赵云、夏侯渊

2、查询一个技能都没有的人员信息（in、EXISTS）

SELECT * FROM t_staff where id not in (SELECT staff_id FROM t_major);
SELECT * FROM t_staff where NOT EXISTS (
    SELECT 1 from t_major where t_staff.id = t_major.staff_id
);

-- 张辽

二、EXISTS、NOT EXISTS（3张表）

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `t_student_course`;
CREATE TABLE `t_student_course` (
  `id` int(11) NOT NULL,
  `sid` varchar(255) NOT NULL,
  `cid` varchar(255) NOT NULL,
  `score` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_student` VALUES (1, '赵子龙');
INSERT INTO `t_student` VALUES (2, '关羽');
INSERT INTO `t_student` VALUES (3, '张飞');
INSERT INTO `t_student` VALUES (4, '黄忠');
INSERT INTO `t_student` VALUES (5, '马超');

INSERT INTO `t_course` VALUES (1, '语文');
INSERT INTO `t_course` VALUES (2, '数学');
INSERT INTO `t_course` VALUES (3, '英语');

INSERT INTO `t_student_course` VALUES (1, 1, 1, 95);
INSERT INTO `t_student_course` VALUES (2, 1, 2, 99);
INSERT INTO `t_student_course` VALUES (3, 1, 3, 100);
INSERT INTO `t_student_course` VALUES (4, 2, 1, 99);
INSERT INTO `t_student_course` VALUES (5, 2, 2, 100);
INSERT INTO `t_student_course` VALUES (6, 3, 2, 100);
INSERT INTO `t_student_course` VALUES (7, 3, 3, 95);
INSERT INTO `t_student_course` VALUES (8, 4, 1, 100);

EXISTS和NOT EXISTS相关子查询什么时候返回，返回有两个条件（很重要）。

1、子查询找到一个匹配的立即返回；
2、子查询遍历所有，没有找到一个匹配的返回为空。

1、查询出选修了全部课程的学生姓名

SELECT name from t_student where id in(
    SELECT sid FROM t_student_course GROUP BY sid 
        HAVING (count(sid)) = (SELECT count(*) from t_course)
)  -- 记录一般写法

SELECT name from t_student s where NOT EXISTS (
    SELECT 1 from t_course c where NOT EXISTS (
        SELECT 1 from t_student_course sc 
            where sc.sid = s.id and sc.cid = c.id
    )
)

-- 赵子龙

子查询只会返回true、false，select后面写1就行了。将查询语句定义成三个变量，后面好解释。

let a1 = SELECT 1 from t_student_course sc 
            where sc.sid = s.id and sc.cid = c.id
let b1 = SELECT 1 FROM t_course WHERE NOT EXISTS (a1)
let c1 = SELECT * FROM t_student WHERE NOT EXISTS (b1);

1  1 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
1  2 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
1  3 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
一个都没找就返回空，最外层的NOT EXISTS就为真，输出

2  1 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
2  2 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
2  3 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

3  1 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

4  1 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
4  2 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

5  1 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

2、查询至少选修了一门课程的学生

SELECT name from t_student s where EXISTS (
    SELECT 1 from t_course c where EXISTS (
        SELECT 1 from t_student_course sc 
              where sc.sid = s.id and sc.cid = c.id
    )
)

-- 赵子龙、关羽、张飞、黄忠

1  1 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

2  1 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

3  1 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
3  2 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

4  1 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

5  1 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
5  2 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
5  3 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
一个都没找就返回空，最外层的EXISTS就为假，不输出

3、查询没有选择所有课程的学生

SELECT name from t_student s where EXISTS (
    SELECT 1 from t_course c where NOT EXISTS (
        SELECT 1 from t_student_course sc 
            where sc.sid = s.id and sc.cid = c.id
    )
)

-- 关羽、张飞、黄忠、马超

1  1 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
1  2 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
1  3 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
一个都没找就返回空，最外层的EXISTS就为假，不输出

2  1 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
2  2 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
2  3 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

3  1 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

4  1 遍历sc表找到一个匹配的立即返回真，最内层的NOT EXISTS就为假，b1为空，不能返回
4  2 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为真，输出

5  1 遍历sc表没有找到一个匹配的返回空（假），最内层的NOT EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的EXISTS就为假，不输出

4、查询一门课也没有选的学生

SELECT name FROM t_student s where NOT EXISTS (
    SELECT 1 from t_course c where EXISTS (
        SELECT 1 from t_student_course sc 
            where sc.sid = s.id and sc.cid = c.id
    )
)

-- 马超

1  1 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

2  1 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

3  1 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
3  2 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

4  1 遍历sc表找到一个匹配的立即返回真，最内层的EXISTS就为真，b1不为空，立即返回
找到一个匹配的立即返回真，最外层的NOT EXISTS就为假，不输出

5  1 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
5  2 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
5  3 遍历sc表没有找到一个匹配的返回空，最内层的EXISTS就为假，b1为空，不能返回
一个都没找就返回空，最外层的NOT EXISTS就为真，输出

5、查询至少选修了学生2选修的全部课程的学生名单

select * from t_student s where id != 2 and NOT EXISTS ( 
    select 1 from t_student_course sc where sid = 2 and NOT EXISTS ( 
        select 1 from t_student_course sc2 
            where sc2.cid = sc.cid and sc2.sid = s.id 
    ) 
)

-- 赵子龙

6、选出每门课程中成绩最高的学生

SELECT * FROM t_student_course sc 
    where sc.score = (SELECT max(score) FROM t_student_course sc2 
                        where sc2.cid = sc.cid)

SELECT * FROM t_student_course sc where NOT EXISTS (
    SELECT 1 FROM t_student_course sc2 
        where sc2.cid = sc.cid and sc2.score > sc.score
)

id sid cid score
3    1    3    100
4    1    4    97
7    2    2    100
8    2    3    100
9    3    1    100
11    4    5    100

最后来说说EXISTS、IN用法
EXISTS查询：先执行一次外部查询，然后为外部查询返回的每一行执行一次子查询，如果外部查询返回100行记录，sql就将执行101次查询。
IN查询：先查询子查询，然后把子查询的结果放到外部查询中进行查询。IN语句在mysql中没有参数个数的限制，但是mysql中sql语句有长度大小限制，整段最大为4M。IN引导的子查询只能返回一个字段。

EXISTS、IN怎么用
当子查询的表大的时候，使用EXISTS可以有效减少总的循环次数来提升速度，当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度，
显然，外表大而子表小时，IN的效率更高，而外表小，子表大时，EXISTS的效率更高，若两表差不多大，则差不多。