sql 中exists 在where中怎样用？

比如在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 。

在子查询中使用 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适合于外表小而内表大的情况。

1.exist,notexist一般都是与子查询一起使用.In可以与子查询一起使用,也可以直接in(a,b.....)\x0d\x0a\x0d\x0a2.exist会针对子查询的表使用索引.notexist会对主子查询都会使用索引.in与子查询一起使用的时候,只能针对主查询使用索引.notin则不会使用任何索引.注意,一直以来认为exists比in效率高的说法是不准确的。\x0d\x0ain是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。\x0d\x0a如果查询的两个表大小相当，那么用in和exists差别不大。\x0d\x0a如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：\x0d\x0a例如：表A（小表），表B（大表）1：select*fromAwhereccin(selectccfromB)\x0d\x0a效率低，用到了A表上cc列的索引；select*fromAwhereexists(selectccfromBwherecc=A.cc)\x0d\x0a效率高，用到了B表上cc列的索引。\x0d\x0a相反的2：select*fromBwhereccin(selectccfromA)\x0d\x0a效率高，用到了B表上cc列的索引；select*fromBwhereexists(selectccfromAwherecc=B.cc)\x0d\x0a效率低，用到了A表上cc列的索引。\x0d\x0anotin和notexists如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；而notextsts的子查询依然能用到表上的索引。所以无论那个表大，用notexists都比notin要快。\x0d\x0a3.exist与in都可以实现一个目的.二者都可以用来过滤数据.\x0d\x0a示例:\x0d\x0a\x0d\x0aselectcount(1)fromt1--160W\x0d\x0aselectcount(1)fromt2--90W\x0d\x0a\x0d\x0aSELECTcount(1)\x0d\x0aFROMt1a\x0d\x0aWHEREEXISTS(SELECTaccountid\x0d\x0aFROMt2b\x0d\x0aWHEREa.keyid=b.keyidANDa.ideaid=b.ideaid)--主大子小,不适合使用exist,因为exist只会利用子表t2的复合索引keyid+ideaid,而子表内容要小与主表,主表由于无法使用索引,查询效率低下.\x0d\x0a\x0d\x0aselectcount(1)fromt1awhereaccountidin(SELECTaccountid\x0d\x0aFROMt2b\x0d\x0aWHEREa.keyid=b.keyidANDa.ideaid=b.ideaid)--主大子小,适合用in,因为in只会使用主表t1里面的复合主键keyid-ideaid,在主表大于子表的情况下,会很好的利用主表的索引.\x0d\x0a\x0d\x0a--后二条sql的执行结果都是一样的.说明exist与in在用法上可以达到一个目的,不同的地方是\x0d\x0a--1.性能的考虑此时就按子表大主表小用exist,子表小主表大用in的原则就可以.\x0d\x0a--2.写法的不同,exist的where条件是:"......whereexist(.....wherea.id=b.id)"\x0d\x0a--in的where条件是:"......whereidin(selectid....wherea.id=b.id)"\x0d\x0a\x0d\x0a4.exist的原理:\x0d\x0aexists做为where条件时，是先对where前的主查询询进行查询，然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出\x0d\x0a比如\x0d\x0a如下：\x0d\x0a表A\x0d\x0aIDNAME\x0d\x0a1A1\x0d\x0a2A2\x0d\x0a3A3\x0d\x0a\x0d\x0a表B\x0d\x0aIDAIDNAME\x0d\x0a11B1\x0d\x0a22B2\x0d\x0a32B3\x0d\x0a\x0d\x0a表A和表B是一对多的关系A.ID-->B.AID\x0d\x0a\x0d\x0aSELECTID,NAMEFROMAWHEREEXISTS(SELECT*FROMBWHEREA.ID=B.AID)\x0d\x0a执行结果为\x0d\x0a1A1\x0d\x0a2A2\x0d\x0a原因可以按照如下分析\x0d\x0aSELECTID,NAMEFROMAWHEREEXISTS(SELECT*FROMBWHEREB.AID=1)\x0d\x0a-->SELECT*FROMBWHEREB.AID=1有值返回真所以有数据\x0d\x0a\x0d\x0aSELECTID,NAMEFROMAWHEREEXISTS(SELECT*FROMBWHEREB.AID=2)\x0d\x0a-->SELECT*FROMBWHEREB.AID=2有值返回真所以有数据\x0d\x0a\x0d\x0aSELECTID,NAMEFROMAWHEREEXISTS(SELECT*FROMBWHEREB.AID=3)\x0d\x0a-->SELECT*FROMBWHEREB.AID=3无值返回真所以没有数据\x0d\x0a\x0d\x0aNOTEXISTS就是反过来\x0d\x0aSELECTID,NAMEFROMAWHERENOTEXIST(SELECT*FROMBWHEREA.ID=B.AID)\x0d\x0a执行结果为\x0d\x0a3A3\x0d\x0a5.in与=的区别\x0d\x0aselectnamefromstudentwherenamein('zhang','wang','li','zhao')\x0d\x0a与\x0d\x0aselectnamefromstudentwherename='zhang'orname='li'orname='wang'orname='zhao'\x0d\x0a的结果是相同的。\x0d\x0ain的字段也可以与其它字段建复合索引.\x0d\x0a比如\x0d\x0aT1包含下面key,accountd,groupid.\x0d\x0a\x0d\x0aSELECT*\x0d\x0aFROMT1a\x0d\x0aWHEREa.groupid=2001\x0d\x0aANDa.accountid=1001\x0d\x0aANDa.keyIN('abc','def','ala')\x0d\x0a\x0d\x0a--上面的sql可以将accountid,key建成复合索引.

两者都能实现表功能查询，主要区别如下：

1、适用表的类型不同。

in是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。

exists是外面的表位驱动表，子查询里面的表为被驱动表，故适用于外面的表结果集小而子查询结果集大的情况。

2、子查询关联不同。

exists一般都是关联子查询。对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。

in则一般都是非关联子查询，非关联子查询则必须先完成内层查询之后，外层查询才能介入。

3、执行次数不同。

IN 语句：只执行一次，确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

EXISTS语句：执行次数根据表的长度而定。指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

参考资料来源：百度百科--In *** 作符

参考资料来源：百度百科--Exists

---