exists用法例子,not exists用法
exists用法例子,not exists用法
语法：
SELECT 字段 FROM table WHERE EXISTS (subquery);
参数：
subquery是一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）
示例：
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
EXISTS执行顺序：
1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A
2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id
3、如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果
语法：
SELECT 字段 FROM table WHERE NOT EXISTS (subquery);
参数：
subquery是一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）
示例：
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
NOT EXISTS执行顺序：
1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A
2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id
3、如果子查询没有返回结果（与EXISTS相反），则NOT EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果
IN常用于where表达式中，其作用是查询某个范围内的数据。
示例：
select * from where field in (value1,value2,value3,…) NOT IN
当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内的选择
示例：
select * from where field not in (value1,value2,value3,…) 实例
假设现在有三张表：
student：学生表，其中有字段sno为学号，sname为学生姓名
course：课程表，其中有字段cno为课程号，cname为课程名称
student_course_relation：选课表，记录学生选择了哪些课程，其中有字段sno为学号，cno为课程号
下面通过几个示例来说明一下EXISTS和NOT EXISTS的用法，及其与IN和NOT IN的区别
1、在子查询中使用NULL，仍然返回结果集
下面三种情况返回数据相同，都会返回student表的所有数据：
select * from student; select * from student where exists (select 1); select * from student where exists (select null);
2、EXISTS子查询返回的是一个布尔值true或false
EXISTS用于检查子查询是否少会返回一行数据，该子查询实际上并不返回任何数据，而是返回布尔值true或false，EXISTS指定一个子查询，检测行的存在。
EXISTS只在乎子查询中是否有记录，与具体的结果集无关，所以下面示例中，子查询中的select sno也可以换成select cno或者select 1，查询出的结果集是一样的。
查询所有选修了课程号为3的学生：
示例，查询所有选修了课程号为1的学生名单
使用exists：
select * from student where exists (select sno from student_course_relation where cno=1 and student_course_relation.sno=student.sno)
使用in：
select * from student where sno in (select sno from student_course_relation where cno=1)
以上两个sql查询，返回相同的结果。
EXISTS查询：先执行一次外部查询，然后为外部查询返回的每一行执行一次子查询，如果外部查询返回100行记录，sql就将执行101次查询。
IN查询：先查询子查询，然后把子查询的结果放到外部查询中进行查询。IN语句在mysql中没有参数个数的限制，但是mysql中sql语句有长度大小限制，整段为4M。IN引导的子查询只能返回一个字段。
当子查询的表大的时候，使用EXISTS可以有效减少总的循环次数来速度，当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来速度，显然，外表大而子表小时，IN的效率更高，而外表小，子表大时，EXISTS的效率更高，若两表差不多大，则差不多。
4、比较使用NOT EXISTS和NOT IN的区别
示例，查询没有选修课程号为1的学生名单
使用NOT EXISTS：
select * from student a where not exists (select sno from student_course_relation b where cno=1 and a.sno=b.sno)
使用NOT IN：
select * from student a where sno not in (select sno from student_course_relation b where cno=1)
NOT EXISTS：先执行一次外部查询，然后为外部查询返回的每一行记录R执行一次子查询，如果子查询没有返回记录，则NOT EXISTS子句返回TRUE，这一行R可作为外部查询的结果行。
NOT IN：外部查询在表中查询每条记录，符要求的就返回结果集，不符的就继续查询下一条记录，直到把表中的记录查询完，也就是说为了证明找不到，需要查询全部记录才能证明，NOT IN不会用到索引。
5、在插入记录前，检查这条记录是否存在，只有当记录不存在时才执行插入操作
示例，若学号为3的学生没有选课程号为2的课程，则选择此课程
insert into student_course_relation(sno, cno) select '3' as sno, '2' as cno from student_course_relation where not exists (select sno from student_course_relation where sno=3 and cno=2) limit 1
此Sql适用场景：虽然业务上具有特性的字段，即使是多个字段的组，也必须建成索引，但是有些老业务可能已经写入了重复数据，且重复数据不能删除，这样的话，就不能建立索引，后续的数据却又要求两个字段的组，可以使用以上sql语句解决这个问题。
6、查询出选修了全部课程的学生姓名
思路1：首先我们需要知道一共有几门课程，然后扫描student_course_relation表，统计出选修了所有课程的学号，在student表中根据学号打出学生姓名。
select sname from student where sno in ( select sno from student_course_relation group by sno -- 根据sno分组，统计每个学生选修了几门课，如果等于course的总数，就是我们要找的sno having(count(sno)) = (select count(*) from course) )
思路2：
首先我们来查询学号为3的学生没有选修的课程
即存在这样的一个学生，他少有一门课没有选
8、查询少选修了学生3选修的全部课程的学生名单
这个题目可以转化为：不存在这样的课程x，学生3选修了x而学生m没有选
即不存在这样的一个学生，他少选修了一门课程