SQL 中的in 语句 IN 与 EXISTS 的区别

先看一下关于 IN 的帮助

 

IN 操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

SQL IN 语法

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

原始的表 (在实例中使用：)

Persons 表:

IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijingIN 操作符实例

现在，我们希望从上表中选取姓氏为 Adams 和 Carter 的人：

我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

结果集：IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon3CarterThomasChangan StreetBeijing IN

确定给定的值是否与子查询或列表中的值相匹配。

语法

test_expression [ NOT ] IN
    (
        subquery
        | expression [ ,...n ]
    )

参数

test_expression

是任何有效的 Microsoft® sql server™ 表达式。

subquery

是包含某列结果集的子查询。该列必须与 test_expression 有相同的数据类型。

expression [,...n]

一个表达式列表，用来测试是否匹配。所有的表达式必须和 test_expression 具有相同的类型。

结果类型

布尔型

结果值

如果 test_expression 与 subquery 返回的任何值相等，或与逗号分隔的列表中的任何 expression 相等，那么结果值就为 TRUE。否则，结果值为 FALSE。

使用 NOT IN 对返回值取反。

示例A. 对比 OR 和 IN

下面的示例选择名称和州的列表，列表中列出所有居住在加利福尼亚、印地安纳或马里兰州的作者。

USE pubs

SELECT au_lname, state
FROM authors
WHERE state = 'CA' OR state = 'IN' OR state = 'MD'

但是，也可以使用 IN 获得相同的结果：

USE pubs

SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')

以下是上面任一查询的结果集：

au_lname   state
--------   -----
White      CA
Green      CA
Carson      CA
O'Leary      CA
Straight      CA
Bennet      CA
Dull      CA
Gringlesby      CA
Locksley      CA
Yokomoto      CA
DeFrance      IN
Stringer      CA
MacFeather      CA
Karsen      CA
Panteley            MD
Hunter            CA
McBadden            CA

(17 row(s) affected)

B. 将 IN 与子查询一起使用

下面的示例在 titleauthor 表中查找从任一种书得到的版税少于 50% 的所有作者的 au_ids，然后从 authors 表中选择 au_ids 与 titleauthor 查询结果匹配的所有作者的姓名。结果显示有一些作者属于得到的版税少于 50% 的一类。

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
   (SELECT au_id
   FROM titleauthor
   WHERE royaltyper < 50)

下面是结果集：

au_lname                                 au_fname             
---------------------------------------- -------------------- 
Green                                    Marjorie             
O'Leary                                  Michael              
Gringlesby                               Burt                 
Yokomoto                                 Akiko                
MacFeather                               Stearns              
Ringer                                   Anne                 

(6 row(s) affected)

C. 将 NOT IN 与子查询一起使用

NOT IN 将找到那些与值列表中的项目不匹配的作者。下面的示例查找至少有一种书取得不少于 50% 的版税的作者姓名：

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id NOT IN
   (SELECT au_id
   FROM titleauthor
   WHERE royaltyper < 50)

下面是结果集：

au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
Carson                                   Cheryl               
Straight                                 Dean                 
Smith                                    Meander              
Bennet                                   Abraham              
Dull                                     Ann                  
Locksley                                 Charlene             
Greene                                   Morningstar          
Blotchet-Halls                           Reginald             
del Castillo                             Innes                
DeFrance                                 Michel               
Stringer                                 Dirk                 
Karsen                                   Livia                
Panteley                                 Sylvia               
Hunter                                   Sheryl               
McBadden                                 Heather              
Ringer                                   Albert               

(17 row(s) affected)

使用 IN 的子查询

通过 IN（或 NOT IN）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这些结果。

下列查询会找到所有曾出版过商业书籍的出版商的名称。

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')

下面是结果集：

pub_name                                 
---------------------------------------- 
Algodata Infosystems                     
New Moon Books                           

(2 row(s) affected)

该语句分两步进行评估。首先，内部查询返回出版过商业书籍的出版商的标识号（1389 和 0736）。然后，这些值被代入外部查询中，在 publishers 中查找与上述标识号相配的名字。

USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表中的列。例如，如果要在结果中包括商业书籍的书名，就必须使用联接来查询。

USE pubs
SELECT pub_name, title
FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id
   AND type = 'business'

下面是结果集：

pub_name               title                                             
---------------------- ------------------------------------------------- 
Algodata Infosystems   The Busy Executive's Database Guide               
Algodata Infosystems   Cooking with Computers: Surreptitious Balance    
                     Sheets                             
New Moon Books         You Can Combat Computer Stress!                   
Algodata Infosystems   Straight Talk About Computers                     

(4 row(s) affected)

该查询显示出联接产生四行，而不是像前面的子查询那样产生两行。

下面是查询的另一个示例，它既可用子查询亦可用联接来表达。该查询查找所有住在 California，并且收到的某本书的版税低于 30 % 的第二作者的姓名。

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE state = 'CA'
   AND au_id IN
      (SELECT au_id
      FROM titleauthor
      WHERE royaltyper < 30
         AND au_ord = 2)

下面是结果集：

au_lname                                 au_fname             
---------------------------------------- -------------------- 
MacFeather                               Stearns              

(1 row(s) affected)

评估内部查询后，产生符合子查询限定条件的三个作者的 ID 号。然后评估外部查询。注意，在内部和外部查询的 WHERE 子句中，都可以包括多个条件。

使用联接，同一查询可以用如下方式表示：

USE pubs
SELECT au_lname, au_fname
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
WHERE state = 'CA'
   AND royaltyper < 30
   AND au_ord = 2

联接总是可以表示为子查询。子查询经常（但不总是）可以表示为联接。这是因为联接是对称的：可以以任意顺序将表 A 联接到表 B，而且会得到相同的答案。而对子查询来说，情况则并非如此。

IN 与 EXISTS  的区别

IN
确定给定的值是否与子查询或列表中的值相匹配。

EXISTS
指定一个子查询，检测行的存在。

比较使用 EXISTS 和 IN 的查询

这个例子比较了两个语义类似的查询。
第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')

-- Or, using the IN clause:

SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')

下面是任一查询的结果集：
pub_name
----------------------------------------
Algodata Infosystems
New Moon Books


exits 相当于存在量词：表示集合存在,也就是集合不为空只作用一个集合.
例如 exist P表示P不空时为真; not exist P表示p为空时为真in表示一个标量和一元关系的关系。
例如：s in P表示当s与P中的某个值相等时 为真; s not in P 表示s与P中的每一个值都不相等时 为真

在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢？

1)性能上的比较
比如Select * from T1 where x in ( select y from T2 )
执行的过程相当于:
select *
  from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

相对的

select * from t1 where exists ( select null from t2 where y = x )
执行的过程相当于:
for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then
         OUTPUT THE RECORD
      end if
end loop
表 T1 不可避免的要被完全扫描一遍

分别适用在什么情况?
以子查询 ( select y from T2 )为考虑方向
如果子查询的结果集很大需要消耗很多时间，但是T1比较小执行( select null from t2 where y = x.x )非常快，那么exists就比较适合用在这里
相对应得子查询的结果集比较小的时候就应该使用in.

in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。

相反的
2：
select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大，用not exists都比not in要快。

in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');

与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'的结果是相同的