EXISTS与IN的区别及用法

[苦苦的潜行者]

当单一条件时

select  * from edepartGeneral where operator in (select operator from edepartdigit )  
//（所影响的行数为 88 行）

select  * from edepartGeneral as a where exists (select * from edepartdigit as b where a.operator=b.operator) 
//（所影响的行数为 88 行）

这两个语句查询结果是一样的


当多个条件时

select  * from edepartGeneral where operator in (select operator from edepartdigit ) and ndate in (select ndate from edepartdigit)
//（所影响的行数为 84 行）

select  * from edepartGeneral as a where exists (select * from edepartdigit as b where a.operator=b.operator and a.ndate=b.ndate)
//（所影响的行数为 75 行）

我百思不得其解,不知道到底是为什么,由于数据库在公司服务器上,所以我没法导出.请高玩指点一二.

[呆到深处自然萌2]

拿公司的sql sever2000服务器试试手,

[苦苦的潜行者]

[Quote=引用 3 楼 ssp2009 的回复:]
SQL code

--这样就一样了
select * from edepartGeneral
where checksum(operator,ndate) in (select checksum(operator,ndate) from edepartdigit )
[/Quote]

真经典啊,一个checksum就解决了同时满足的时候.

-----------------------
[引用6,8楼的回复]

嗯,都正确,你们几个的回答让我明白了我到底错在哪里.
--------------------------
假如
在edepartgeneral里面
有这样的数据

ndate       operator
2011-09-01  aaa
2011-09-01  aaa
2011-09-02  aaa
2011-09-02  aaa
2011-09-02  aaa
2011-09-03  aaa
2011-09-03  aaa
2011-09-03  aaa

在edepartdigit里面
有这样的数据

ndate       operator
2011-09-01  aaa
2011-09-02  aaa
2011-09-02  aaa
2011-09-03  aaa

这样导致了我问题中的错误,

综合3.6.8楼的回复看还是说条件如果要分开写就都分开,要合起来(如3楼的checksum)就都合起来这样才不会出错,是不是?

--------------------------
希望有人帮我插楼,我还要很多问题继续问呢.

[-晴天]

结帖了再问哦!

[苦苦的潜行者]

[Quote=引用 2 楼 acherat 的回复:]
in表示操作的该列要在子查询的结果集中。

exists表示满足条件的子查询是否存在记录，只要存在即为真，类似JAVA中的boolean判断。
[/Quote]

虽然我明白哪里出错了,但是对你的回复还是有点懵懂.
就你说的"in表示操作的该列要在子查询的结果集中"
我写了如下语句(先抛开大神给出已经解决的方案)

select  * from edepartGeneral where operator in (select operator from edepartdigit where ndate in(select * from edepartGeneral) )

出现错误:服务器: 消息 116，级别 16，状态 1，行 1
当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。
为什么呢...

[bigbluefox]

诸位楼上的分析的都不错，在这种查询情况下，使用Exists应该效率更高一下，在Exists情况下，只要找到一个匹配的，就会跳到下一个对比，注意一下查询条件是否完全匹配。

[苦苦的潜行者]

[Quote=引用 15 楼 qianjin036a 的回复:]
结帖了再问哦!
[/Quote]

还是这个问题,我要把网友给出的答案好好看看,对他的答案有疑问还得继续问他啊,结贴还不至于吧

[苦苦的潜行者]

[color=#FF0000]请无视16楼[/color]
--------------
引用 2 楼 acherat 的回复:
in表示操作的该列要在子查询的结果集中。

exists表示满足条件的子查询是否存在记录，只要存在即为真，类似JAVA中的boolean判断。



虽然我明白哪里出错了,但是对你的回复还是有点懵懂.
就你说的"in表示操作的该列要在子查询的结果集中"
我写了如下语句(先抛开大神给出已经解决的方案)

SQL codeselect  * from edepartGeneral where operator in (select operator from edepartdigit where ndate in(select ndate from edepartGeneral) )
//（所影响的行数为 90 行）

为什么呢...

[AcHerat]

SQL codeselect  * from edepartGeneral where operator in (select operator from edepartdigit where ndate in(select ndate from edepartGeneral) )
//（所影响的行数为 90 行）

说明符合条件的结果集的记录有90条。

[AcHerat]

这种查询楼主按集合的交集来理解会容易点，不同的是选择交集的条件根据子查询的条件而不同。

[苦苦的潜行者]

[Quote=引用 20 楼 acherat 的回复:]
SQL code
SQL codeselect * from edepartGeneral where operator in (select operator from edepartdigit where ndate in(select ndate from edepartGeneral) )
//（所影响的行数为 90 行）



说明符合条件的结果集的记录有90条。
[/Quote]

....
姐我错了,...

我想知道这句是查到什么东西和 0楼的语句有什么不同之处

[苦苦的潜行者]

select  * from edepartGeneral where operator in (select operator from edepartdigit where ndate in(select ndate from edepartGeneral) )
//（所影响的行数为 90 行）

由于edepartgeneral和edepartdigit中operator只有一个是相同的,即为"小王"
所以我查了一下

select  * from edepartGeneral where operator='小王'
//（所影响的行数为 90 行）
select distinct  * from edepartGeneral where operator='小王'
//（所影响的行数为 90 行）

好吧,自己跟自己纠结然后纠结回来原点,牛B

[AcHerat]

0楼帖：

查询类似

select *
from tb
where 条件A and 条件B

这里是在最外部与的关系

19楼帖：

查询类似

select *
from tb
where 条件A

注意，这里的条件A是具体是表示在子查询的时候按条件B筛选之后的结果集！

例子看看就知道了：

表 t1

id ----- bid ----- status
1 ------- 1 --------- 0
1 ------- 2 --------- 0
2 ------- 2 --------- 1
3 ------- 3 --------- 0

表 t2

id ------ status
1 --------- 0
2 --------- 0

查询语句

SQL1：

select *
from t1
where bid in (select id from t2) and status in (select status from t2)

结果集中 t1 表数据出来的会有

id ----- bid ----- status
1 ------- 1 --------- 0
1 ------- 2 --------- 0


SQL2：

select *
from t1
where bid in (select id from t2 where status in (select status from t1))

先看in后括号中的子查询，结果集会是什么？ 楼主可以按例子看看，最后会是 表t2 id 为 1 2 的数据
那么外部查询的意义就是 bid in (1,2) 出来的结果集会是 表t1 数据的

id ----- bid ----- status
1 ------- 1 --------- 0
1 ------- 2 --------- 0
2 ------- 2 --------- 1

多出来一条，不知道楼主理解没！

[苦苦的潜行者]

[Quote=引用 10 楼 ssp2009 的回复:]
你的in用的不到位，跟用法没关系。
[/Quote]

回过头来我又自己捋了捋,然后又实际的拿公司数据库实验了一上午,发现0楼的in and in用法确实是有问题的.而且也想不出in and in 到底有什么实际意义.如果只是查询一种条件还好理解,如果查询两种条件,还导致这样的歧义,所以说in and in的用法还是欠妥当的.我的本意是查询两个表中都相同的两者.只是想不出in同时查询两个条件该怎么用,所以意外的想出这样的错误用法,不过也好,至少我明白了不少其中的差异.

[回复5楼]
谢谢给我讲解为什么大多数时候in的效率比exists低的原因.
[回复12楼]
你的例子我测试了,确实如此,谢谢耐心指点.

[回复24楼]
对于你回答我的19楼的问题,很详细很好理解,谢谢了.

//------------------------

[回复26楼]
你的回答貌似很专业的样子,都是高深的词汇.我今天也在看3楼checksum也是说什么哈希值,哈希索引的.我还需要慢慢消化.
至于你的1中的

select * from t1 where c2 not in (select c2 from t2);                  --返回null

我怎么感觉结果是1 . 3 呢,我去测试的时候为什么却是和你说的一样没有结果呢

[大力水手]

[Quote=引用 26 楼 public0011 的回复:]

1. exists 和 in 的最主要的区别是...exists是俩值逻辑.(真假) in 是三值逻辑(真,假,空) 这一点在exists和in中体现不是很明显.但是如果用not in 和not exists 就非常明显了..
例如:
SQL code

create table t1 (c1 int,c2 int);
create table t2 (c1 int,c2 int);

……
[/Quote]

exists(真假) in(真假空)
引用T-sql查询一书:
假设查询消费者列表是(a,b,null) 消费者a 位于列表中
a in (a,b,null) 返回true
c 不包含在列表中 但是因为列表里有一个null c in(a,b,null) 返回 unknown 不知道
c not in(a,b,null) 返回not unknown 等价于unknown
你可以这样认为,c不管是任何数他都会返回not nuknown

-- 我语言组织能力差类一笔..不知道你能不能看明白!

[hongjian39400]

与in与Exist无关。
楼主用in的思路是：A表中的a字段值在B的a有相等值，且此行的b字段值在都存在则select出来。
A
a b
‘1’ ‘11’
B
a b
‘2’‘11’
‘1’‘22’
in可以select 1行
exists 则是 0行

[W9757]

7楼正解.

[oO寒枫Oo]

举个简单的例子
a表里面的记录是
1,2

b表里面的是
1,3
2,2

用 2个in的话是能够查出来的 而exists 是没有记录的
2个in在这里的意思并不是2列的条件必须同时满足 而exists 是必同时满足的

还有一种和exists等效的办法 就是inner join

[xiaolll028]

扯到哪里去了

[大力水手]

1. exists 和 in 的最主要的区别是...exists是俩值逻辑.(真假) in 是三值逻辑(真,假,空) 这一点在exists和in中体现不是很明显.但是如果用not in 和not exists 就非常明显了..
例如:

create table t1 (c1 int,c2 int);
create table t2 (c1 int,c2 int);

insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);

select * from t1 where c2 not in (select c2 from t2);                  --返回null
select * from t1 where not exists (select * from t2 where t1.c2=t2.c2); --返回1,3
--上面一句等价于等价于下面一句
select * from t1 where c2 not in (select c2 from t2 where c2 is not null)-- 返回1,3

2. in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询。
这样的话，in适合内外表都很大的情况，exists适合外表结果集很小的情况。很多网上和书上绝对的说
in 的效率绝对比exists 的效率低那是不对的..如果那样的话...微软可以把in 这个东西去掉了..完全用
exists代替 希望对你有帮助...

[苦苦的潜行者]

[Quote=引用 28 楼 public0011 的回复:]
引用 26 楼 public0011 的回复:

1. exists 和 in 的最主要的区别是...exists是俩值逻辑.(真假) in 是三值逻辑(真,假,空) 这一点在exists和in中体现不是很明显.但是如果用not in 和not exists 就非常明显了..
例如:
SQL code

create table t1 (c1 int,c2 int);
create ……
[/Quote]

哦，原来如此，那么说in在判断上还是有误差的，看来以后尽量少用in了