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.idlet 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 score313100414977221008231009311001145100

最后来说说EXISTS、IN用法

EXISTS查询：先执行一次外部查询，然后为外部查询返回的每一行执行一次子查询，如果外部查询返回100行记录，sql就将执行101次查询。

IN查询：先查询子查询，然后把子查询的结果放到外部查询中进行查询。IN语句在mysql中没有参数个数的限制，但是mysql中sql语句有长度大小限制，整段最大为4M。IN引导的子查询只能返回一个字段。

EXISTS、IN怎么用

当子查询的表大的时候，使用EXISTS可以有效减少总的循环次数来提升速度，当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度，

显然，外表大而子表小时，IN的效率更高，而外表小，子表大时，EXISTS的效率更高，若两表差不多大，则差不多。