转载:http://blog.csdn.net/lihan6415151528/archive/2009/08/20/4466914.aspx
exists (sql 返回结果集为真)
not exists (sql 不返回结果集为真)
如下:
表A
ID NAME
1 A1
2 A2
3 A3
表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
表 A和表B是1对多的关系 A.ID => B.AID
SELECT ID,NAME FROM A WHERE EXIST ( SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 1 )
-- ->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 2 )
-- ->SELECT * FROM B WHERE B.AID=2有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 3 )
-- ->SELECT * FROM B WHERE B.AID=3无值返回真所以没有数据
NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE NOT EXIST ( SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN ,意思相同不过语法上有点点区别,好像使用IN效率要差点,应该是不会执行索引的原因
SELECT ID,NAME FROM A WHERE ID IN ( SELECT AID FROM B)
NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE ID NOT IN ( SELECT AID FROM B)
下面是普通的用法:
SQL中IN, NOT IN , EXISTS , NOT EXISTS的用法和差别:
IN :确定给定的值是否与子查询或列表中的值相匹 配。
IN 关键字使您得以选择与列表中的任意一个值匹配的行。
当要获得居住在 California、Indiana 或 Maryland 州的所有作者的姓名和州的列表时,就需要下列查询:
SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5
然而,如果使用 IN ,少键入一些字 符也可以得到同样的结果:
SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN ( 1 , 4 , 5 )
IN 关键字之后的项目必须用逗号隔开,并且括在括号中。
下列查询在 titleauthor 表中查找在任一种书中得到的版税少于 50 % 的所有作者的 au_id,然后从 authors 表中选择 au_id 与
titleauthor 查询结果匹配的所有作者的姓名:
SELECT au_lname, au_fname FROM authors WHERE au_id IN ( SELECT au_id FROM titleauthor WHERE royaltyper < 50 )
结果显示有一些作者属于少于 50 % 的一类。
NOT IN :通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。
以下查询查找没有出版过商业书 籍的出版商的名称。
SELECT pub_name FROM publishers WHERE pub_id NOT IN ( SELECT pub_id FROM titles WHERE type = ' business ' )
使用 EXISTS 和 NOT EXISTS 引入的子查询可用于两种集合原理的操作:交集与差集。
两个集合的交集包含同时属于两个原集合的所有元素。
差集包含只属于两个集合中的第一个集合的元素。
EXISTS :指定一个子查询,检测行的存在。
本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:
SELECT DISTINCT pub_name FROM publishers WHERE EXISTS ( SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
' business ' )
SELECT distinct pub_name FROM publishers WHERE pub_id IN ( SELECT pub_id FROM titles WHERE type = ' business ' )
两者的区 别:
EXISTS :后面可以是整句的查询语句如: SELECT * FROM titles
IN :后面只能是对 单列: SELECT pub_id FROM titles
NOT EXISTS :
例如,要查找不出版商业书籍的出版商的名称:
SELECT pub_name FROM publishers WHERE NOT EXISTS ( SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
' business ' )
下面的查 询查找已经不销售的书的名称:
SELECT title FROM titles WHERE NOT EXISTS ( SELECT title_id FROM sales WHERE title_id = titles.title_id)
语法
EXISTS subquery
参数
subquery:是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。有关更多信息,请参见 SELECT 中有关子查询的讨论。
结果类型:Boolean
结果值:如果子查询包含行,则返回 TRUE。
示例
A. 在子查询中使用 NULL 仍然返回结果集
这 个例子在子查询中指定 NULL ,并返回结果集,通过使用 EXISTS 仍取值为 TRUE。
USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE EXISTS ( SELECT NULL )
ORDER BY CategoryName ASC
GO
B. 比较使用 EXISTS 和 IN 的查询
这个例 子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN 。注意两个查询 返回相同的信息。
USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
( SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = \ ' business\ ' )
GO
-- Or, using the IN clause:
USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
( SELECT pub_id
FROM titles
WHERE type = \ ' business\ ' )
GO
下面是任一查询的 结果集:
pub_name
-- --------------------------------------
Algodata Infosystems
New Moon Books
C.比较使用 EXISTS 和 = ANY 的查询
本示例显示查找 与出版商住在同一城市中的作者的两种查询方法:第一种方法使用 = ANY ,第二种方法使用 EXISTS 。注意这两种方法返回相同的信息。
USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
( SELECT *
FROM publishers
WHERE authors.city = publishers.city)
GO
-- Or, using = ANY
USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
( SELECT city
FROM publishers)
GO
D.比较使用 EXISTS 和 IN 的查询
本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:
USE pubs
GO
SELECT title
FROM titles
WHERE EXISTS
( SELECT *
FROM publishers
WHERE pub_id = titles.pub_id
AND city LIKE \ ' B%\ ' )
GO
-- Or, using IN:
USE pubs
GO
SELECT title
FROM titles
WHERE pub_id IN
( SELECT pub_id
FROM publishers
WHERE city LIKE \ ' B%\ ' )
GO
E. 使用 NOT EXISTS
NOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行,则满足 NOT EXISTS 中的 WHERE 子句。本示例查找不出版商业书籍的出版商的名称:
USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
( SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = \ ' business\ ' )
ORDER BY pub_name
GO