比如在Northwind数据库中有一个查询为
SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)
这内里的EXISTS是怎样运作呢?子查询返回的是OrderId字段,然则表面的查询要找的是CustomerID和CompanyName字段,这两个字段一定不在OrderID内里啊,这是怎样婚配的呢?
EXISTS用于搜检子查询是不是最少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False
EXISTS 指定一个子查询,检测 行 的存在。
语法: EXISTS subquery
参数: subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 症结字)。
效果范例: Boolean 假如子查询包含行,则返回 TRUE ,不然返回 FLASE 。
例表A:TableIn | 例表B:TableEx |
(一). 在子查询中运用 NULL 依然返回效果集
select * from TableIn where exists(select null) 等同于: select * from TableIn
(二). 比较运用 EXISTS 和 IN 的查询。注重两个查询返回雷同的效果。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME) select * from TableIn where ANAME in(select BNAME from TableEx)
(三). 比较运用 EXISTS 和 = ANY 的查询。注重两个查询返回雷同的效果。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME) select * from TableIn where ANAME=ANY(select BNAME from TableEx)
NOT EXISTS 的作用与 EXISTS 恰好相反。假如子查询没有返回行,则满足了 NOT EXISTS 中的 WHERE 子句。
结论:
EXISTS(包含 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...), 我将其称为EXIST的内查询语句。其内查询语句返回一个效果集。 EXISTS子句依据其内查询语句的效果集空或许非空,返回一个布尔值。
一种浅显的能够理解为:将外查询表的每一行,代入内查询作为磨练,假如内查询返回的效果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的效果行,不然不能作为效果。
剖析器会先看语句的第一个词,当它发明第一个词是SELECT症结字的时刻,它会跳到FROM症结字,然后经由过程FROM症结字找到表名并把表装入内存。接着是找WHERE症结字,假如找不到则返回到SELECT找字段剖析,假如找到WHERE,则剖析个中的前提,完成后再回到SELECT剖析字段。末了构成一张我们要的虚表。
WHERE症结字背面的是前提表达式。前提表达式盘算完成后,会有一个返回值,即非0或0,非0即为真(true),0即为假(false)。同理WHERE背面的前提也有一个返回值,真或假,来肯定接下来执不实行SELECT。
剖析器先找到症结字SELECT,然后跳到FROM症结字将STUDENT表导入内存,并经由过程指针找到第一条纪录,接着找到WHERE症结字盘算它的前提表达式,假如为真那末把这条纪录装到一个虚表当中,指针再指向下一条纪录。假如为假那末指针直接指向下一条纪录,而不举行别的操纵。一向检索完悉数表,并把检索出来的假造表返回给用户。EXISTS是前提表达式的一部分,它也有一个返回值(true或false)。
在插进去纪录前,须要搜检这条纪录是不是已存在,只有当纪录不存在时才实行插进去操纵,能够经由过程运用 EXISTS 前提句防备插进去重复纪录。
INSERT INTO TableIn (ANAME,ASEX)
SELECT top 1 '张三', '男' FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)
EXISTS与IN的运用效力的题目,通常状况下采纳exists要比in效力高,由于IN不走索引,但要看实际状况细致运用:
IN适合于表面大而内表小的状况;EXISTS适合于表面小而内表大的状况。
in、not in、exists和not exists的区分:
先谈谈in和exists的区分:
exists:存在,背面平常都是子查询,当子查询返回行数时,exists返回true。
select * from class where exists (select'x"form stu where stu.cid=class.cid)
当in和exists在查询效力上比较时,in查询的效力快于exists的查询效力
exists(xxxxx)背面的子查询被称做相干子查询, 他是不返回列表的值的.
只是返回一个ture或false的效果(这也是为何子查询里是select 'x'的缘由 固然也能够
select任何东西) 也就是它只在乎括号里的数据能不能查找出来,是不是存在如许的纪录。
其运转体式格局是先运转主查询一次 再去子查询里查询与其对应的效果 假如存在,返回ture则输
出,反之返回false则不输出,再依据主查询中的每一行去子查询里去查询.
实行递次以下:
1.起首实行一次外部查询
2.关于外部查询中的每一行离别实行一次子查询,而且每次实行子查询时都邑援用外部查询中当
前行的值。
3.运用子查询的效果来肯定外部查询的效果集。
假如外部查询返回100行,SQL 就将实行101次查询,一次实行外部查询,然后为外部查询返回
的每一行实行一次子查询。
in:包含
查询和一切女生岁数雷同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
in()背面的子查询 是返回效果集的,换句话说实行序次和exists()不一样.子查询先发生效果集,
然后主查询再去效果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
not in和not exists的区分:
not in 只有当子查询中,select 症结字后的字段有not null束缚或许有这类暗示时用not in,别的假如主查询中表大,子查询中的表小然则纪录多,则应该运用not in,
比方:查询那些班级中没有门生的,
select * from class where cid not in(select distinct cid from stu)
当表中cid存在null值,not in 不对空值举行处置惩罚
处理:select * from class
where cid not in
(select distinct cid from stu where cid is not null)
not in的实行递次是:是在表中一条纪录一条纪录的查询(查询每条纪录)符合要求的就返回效果集,不符合的就继承查询下一条纪录,直到把表中的纪录查询完。也就是说为了证实找不到,所以只能查询悉数纪录才证实。并没有用到索引。
not exists:假如主查询表中纪录少,子查询表中纪录多,并有索引。
比方:查询那些班级中没有门生的,
select * from class2
where not exists
(select * from stu1 where stu1.cid =class2.cid)
not exists的实行递次是:在表中查询,是依据索引查询的,假如存在就返回true,假如不存在就返回false,不会每条纪录都去查询。
之所以要多用not exists,而不必not in,也就是not exists查询的效力远远高与not in查询的效力。
实例:
exists,not exists的运用方法示例,须要的朋侪能够参考下。
门生表:create table student ( id number(8) primary key, name varchar2(10),deptment number(8) )
选课表:create table select_course ( ID NUMBER(8) primary key, STUDENT_ID NUMBER(8) foreign key (COURSE_ID) references course(ID), COURSE_ID NUMBER(8) foreign key (STUDENT_ID) references student(ID) )
课程表:create table COURSE ( ID NUMBER(8) not null, C_NAME VARCHAR2(20), C_NO VARCHAR2(10) )
student表的数据: ID NAME DEPTMENT_ID ---------- --------------- ----------- 1 echo 1000 2 spring 2000 3 smith 1000 4 liter 2000
course表的数据: ID C_NAME C_NO ---------- -------------------- -------- 1 数据库 data1 2 数学 month1 3 英语 english1
select_course表的数据: ID STUDENT_ID COURSE_ID ---------- ---------- ---------- 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 3 2
1.查询选修了一切课程的门生id、name:(即这一个门生没有一门课程他没有选的。)
剖析:假如有一门课没有选,则此时(1)select * from select_course sc where sc.student_id=ts.id
and sc.course_id=c.id存在null,
这说明(2)select * from course c 的查询效果中确切有纪录不存在(1查询中),查询效果返回没有选的课程,
此时select * from t_student ts 后的not exists 推断效果为false,不实行查询。
SQL> select * from t_student ts where not exists (select * from course c where not exists (select * from select_course sc where sc.student_id=ts.id and sc.course_id=c.id));
ID NAME DEPTMENT_ID ---------- --------------- ----------- 1 echo 1000
2.查询没有挑选一切课程的门生,即没有全选的门生。(存在如许的一个门生,他最少有一门课没有选), 剖析:只需有一个门没有选,即select * from select_course sc where student_id=t_student.id and course_id =course.id 有一条为空,即not exists null 为true,此时select * from course有查询效果(id为子查询中的course.id ), 因而select id,name from t_student 将实行查询(id为子查询中t_student.id )。 SQL> select id,name from t_student where exists (select * from course where not exists (select * from select_course sc where student_id=t_student.id and course_id=course.id)); ID NAME ---------- --------------- 2 spring 3 smith 4 liter 3.查询一门课也没有选的门生。(不存如许的一个门生,他最少选修一门课程), 剖析:假如他选修了一门select * from course效果集不为空,not exists 推断效果为false; select id,name from t_student 不实行查询。 SQL> select id,name from t_student where not exists (select * from course where exists (select * from select_course sc where student_id=t_student.id and course_id=course.id)); ID NAME ---------- --------------- 4 liter 4.查询最少选修了一门课程的门生。 SQL> select id,name from t_student where exists (select * from course where exists (select * from select_course sc where student_id=t_student.id and course_id=course.id)); ID NAME ---------- --------------- 1 echo 2 spring 3 smith
本文引见了SQL中EXISTS的用法 ,更多相干内容请关注ki4网。
相干引荐:
Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
Mysql经常使用基准测试东西
Mysql函数 的相干解说
以上就是SQL中EXISTS的用法的细致内容,更多请关注ki4网别的相干文章!