exists和in的区别

Question

一直很疑惑exist的用法，他在比较
比如说两张表一张是用户表TDefUser（userid，address,phone），一张是消费表TAccConsume(userid,time,amount)，我要查消费超过5000的用户记录，那么我可以写
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)
也可以写
select * from TDefUser
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)据说是exists的效率比较好，可是我不太知道exists的执行过程是怎么样的，它会把两张表join么？
它的True和False是怎么返回的，怎么知道exists判断的是那列，我没有指定列阿？
select 1 代表什么意思呢？这个问题困挠我好久了，请帮帮忙

Answer 1

select 1 代表什么意思呢？
＝＝＝＝＝＝＝＝＝＝＝＝＝＝
师傅说因为exists只返回真或假，select 1 可以提高效率。

Answer 2

exists 只會反回true或者false兩個值

Answer 3

谢谢mengmou(孟某) ( )
那是否意味着我把1改成任何一个其他的值效率都一样呢
比如说select * from TDefUser
where exists (select 5 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)
 

Answer 4

忘了是从哪个文档里看过了，说是最好用exists 不用in，其实一般用in 的地方都可以转化为exists 的，只是可能要写的长一些．具体原因，忘了．
可能原因是１是效率，２是ＭＳ可能在未来的版本不支持in了．３是现在的in 是ＭＳ向老版本mssql兼容用的，４．．．．．

Answer 5

where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)意思是只有表里有记录我就返回一个1  就可以了where exists (select * from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)和这种相比 效率肯定高很多吗！
这是数据有记录的话 我还要把所以的记录都返回回来！他们这这样的一个比较 不是1和5 的比较！

Answer 6

exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因 当然也可以select任何东西)
其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

Answer 7

exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因 当然也可以select任何东西)
其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

Answer 8

可是我不太知道exists的执行过程是怎么样的
------------------------------------------
执行顺序如下：
1.首先执行一次外部查询
2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。
3.使用子查询的结果来确定外部查询的结果集。
如果外部查询返回100行，SQL 就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。但实际上，SQL的查询
优化器有可能会找到一种更好的方法来执行相关子查询，而不需要实际执行101次查询。

Answer 9

in()里面的子查询是返回一个结果集，如下语句
select a.code,a.name from table a
where a.code in(select b.code from table b where a.name like 'A')select b.code from table b where a.name like 'A'这名返回的是一个结果集，在执行上面的语句时，首先从TABLE表中取一条记录，再看这条记录的CODE记录是否在子查询返回的结果集中。如当前取得是‘００１’，子查询返回的结果集是
001
002
003
则条件条件。而exists（）返回是一个逻辑值，如果子查询有结果值则返回TRUE，否则返回FALSE
主查询检查返回的结果值决定是否要当前的记录行。一般EXISTS的执行效率高一些。

Answer 10

比如用户表TDefUser（userid，address,phone），消费表TAccConsume(userid,time,amount)消费表聚集索引是userid,time
数据（注意因为有聚集索引,实际存储也是按以下次序的）
1   2006-1-1  200
1   2006-1-2  300
1   2006-1-2  500
1   2006-1-3  2000
1   2006-1-3  2000
1   2006-1-4  400
1   2006-1-5  500
2   2006-1-1  200
2   2006-1-2  300
2   2006-1-2  500
2   2006-1-3  2000
2   2006-1-3  6000
2   2006-1-4  400
2   2006-1-5  8000
3   2006-1-1  7000
3   2006-1-2  30000
3   2006-1-2  50000
3   2006-1-3  20000语句：
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)对于userid=1，需要找所有记录，才返回false，与第三个语句的效率差不多
对于userid=2，找到2006-1-3的记录，就返回true，比第三个语句的效率高
对于userid=3，第一条记录就返回true，比第三个语句的效率高语句
select * from TDefUser
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)返回空记录集
2
2
3
3
3
3再判断语句
select * from TDefUser
where userid in (select userid from TAccConsume where userid=TDefUser.userid and amount>5000)对于userid=1，需要找所有记录，返回空记录集，比较判断
对于userid=2，需要找所有记录，返回记录集 
2
2
，比较判断
对于userid=3需要找所有记录，返回记录集 
3
3
3
3
，比较判断

Answer 11

表中如果没有聚集索引，对exists每个userid查找的条数都不同，但都是<=第三个语句需要扫描的条数，极端的（比如>5000的都是在最后）与第三个语句效率相似，一般的比第三个语句快，所以说,“一般”exists比in效率高

Answer 12

罗罗嗦嗦这么多，想说明exists(not exists)的效率是和表的数据有关的、也和表的数据的排列有关

Answer 13

in效率肯定不如exists ,我以前的帖子里有说过的,我们公司也要求尽量用exists 而不用in

Answer 14

我在论坛上经常看到关于exsis与in效率的问题,大多数都说exsis比in效率高,昨天我把我的一个SQL2K的一个视图改了,原来用in的,现改为用exsis,同样的结果,数据只有4235行,用in不用1秒,用exsis耗时23秒.大侠们请分析分析:
用IN(耗时0.01秒):SELECT *
FROM dbo.判定结果
WHERE ((工程编号 + 项目编号) IN
          (SELECT b.工程编号 + b.项目编号
         FROM 判定结果 AS b
         GROUP BY b.工程编号, b.项目编号
         HAVING SUM(b.检测项数) > 0))用EXSIS(耗时23秒):SELECT *
FROM dbo.判定结果 a
WHERE EXISTS
          (SELECT 1
         FROM dbo.判定结果 AS b WHERE a.工程编号 + a.项目编号 = b.工程编号 + b.项目编号
         GROUP BY b.工程编号, b.项目编号
         HAVING SUM(b.检测项数) > 0
      )

Answer 15

b615n(周围走):1、原来用((工程编号 + 项目编号) IN 可能有两个原因，
a、是因为要用in，只能选择一个结果，被迫把两个字段连接在一起的，那就可以改成如下：
SELECT *
FROM dbo.判定结果 a
WHERE EXISTS
          (SELECT 1
         FROM dbo.判定结果 AS b WHERE a.工程编号  = b.工程编号 
         and a.项目编号= b.项目编号
         GROUP BY b.工程编号, b.项目编号
         HAVING SUM(b.检测项数) > 0
      )
如果(工程编号,项目编号)有索引，那这个语句会用到索引，数据量大的时候exists的效率会大很多，如果检测项数都是非负数，还可以改为：
SELECT *
FROM dbo.判定结果 a
WHERE EXISTS
          (SELECT 1
         FROM dbo.判定结果 AS b WHERE a.工程编号  = b.工程编号 
         and a.项目编号= b.项目编号
         and b.检测项数 > 0
      )
效果更明显b、很特殊的，比如（工程编号=01，项目编号=1011）和（工程编号=011，项目编号=011）当作相同的，也就是说必须用 (工程编号 + 项目编号)来判断，那肯定用不到索引了
至于1秒和23秒，我想一方面数据不多，本来效率差别不大，另一方面，会不会和测试有关，同一语句多执行几次，以不要计算第一次的平均为准来测试才公平。

Answer 16

in 时,查询需要比对.
Exists 时,不需要比对,
所以 Exists比in效率要高.

Answer 17

我的理解是用哪个关键字能够减少查询次数，同样查询次数能够减少返回信息量，能够很好的利用索引，那么我们就用那个。
exists在查询的时候只要有一个记录符合条件，那么它就返回true，这样查询次数少，信息返回量也少。而in是返回所有符合条件的记录集。所以一般exists比in好。
但是 b615n(周围走) 又举了个例子，我相信他测试的结果是正确的，造成这种结果的原因也很正常，在数据量不是太大的情况下，in很好的利用了索引所有查询效率高。这点很重要，合理利用索引对擦选效率有很大的提高。