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没有选

即不存在这样的一个学生，他少选修了一门课程