今天说一下where 中 exists 和 in 里面的一些区别

时间:2021-01-12 14:59:40

in 和 exists 已经成为我们日常查询时候的常客了。很多时候他们2个都是可以互通实现的,但是,无论兄弟怎么亲,还是会有那么一些差别的。

先搞个测试表

CREATE TABLE #Tmp1(ID INT,Col1 NVARCHAR(50))

CREATE TABLE #Tmp2(ID INT,T1ID INT,Col1 NVARCHAR(50),Col2 NVARCHAR(50))

INSERT INTO #Tmp1
( ID, Col1 )
VALUES ( 1, N'AAA'),( 2, N'BBB'),( 3, N'CCC'),( 4, N'DDD') INSERT INTO #Tmp2
( ID, T1ID, Col1, Col2 )
VALUES  ( 1, 1, N'aa1',N'aa!!'),( 5, 1, N'aa2',N'vv!!'),( 6, 3, N'cc3',N'VV!!')

然后一般来说,查#Tmp1 的ID存在于#Tmp2里面的数据,以下2个语句都是等价的~

SELECT * FROM #Tmp1 a
WHERE EXISTS(SELECT * FROM #Tmp2 WHERE a.ID = T1ID) SELECT * FROM #Tmp1
WHERE ID IN (SELECT T1ID FROM #Tmp2) ID Col1
----------- --------------------------------------------------
1 AAA
3 CCC ID Col1
----------- --------------------------------------------------
1 AAA
3 CCC

看~查到的是一样的。然后现在要给点注意一点的地方了~

1、 使用exists 的时候因为exists后面跟的只是一个bool ,所以在exists() 括号里面跟在 select 和from之间,其实写什么都不重要,只要条件有结果返回,就OK。比方说

SELECT * FROM #Tmp1 a
WHERE EXISTS(SELECT NULL FROM #Tmp2 WHERE a.ID = T1ID) SELECT * FROM #Tmp1 a
WHERE EXISTS(SELECT * FROM #Tmp2 WHERE 1=0) ID Col1
----------- --------------------------------------------------
1 AAA
3 CCC ID Col1
----------- --------------------------------------------------

先看第二句,因为子查询里面是一个恒假的表达式,并不返回任何行数,所以,外部的查询结果一行都不会出来。这个好理解。

第一句即使你填的是select null 也没所谓,因为只要有结果集返回,就OK了,甚至改成

SELECT * FROM #Tmp1 a
WHERE EXISTS(SELECT null) ID Col1
----------- --------------------------------------------------
1 AAA
2 BBB
3 CCC
4 DDD
也全部返回了。为什么,因为子查询里面每一个都有一行 null 返回出来,那 exists()的判定就是真。所以每一行都会被返回。这个是要注意的。

2 使用 in 的注意事项。
使用 in 的注意事项最主要是在相关子查询上面,非相关子查询倒是没有什么要特别注意的。但是有一个原则

比方说将上面一个语句改成一个相关子查询,在子查询里面引用了#Tmp1 的ID,然后就变成以下的结果

SELECT * FROM #Tmp1 a
WHERE ID IN (SELECT T1ID FROM #Tmp2 WHERE a.ID = T1ID) ID Col1
----------- --------------------------------------------------
1 AAA
3 CCC

感知不明显是吧,再改动一下,以下这句在实际中并大部分情况并没有任何的意义,只是用于示例。

SELECT * FROM #Tmp1 a
WHERE ID IN (SELECT ID FROM #Tmp2 ) ID Col1
----------- --------------------------------------------------
1 AAA

看看这个语句,从执行结果来看,你可以知道是#Tmp2里面的ID,但有没有怀疑过是#Tmp1 自身的ID呢?能够引用吗?可以!!所以如以下栗子,有时候真会出现这种情况的哟~小心小心再小心哦~

SELECT * FROM #Tmp1 a
WHERE ID IN (SELECT a.ID FROM #Tmp2 ) ID Col1
----------- --------------------------------------------------
1 AAA
2 BBB
3 CCC
4 DDD

3、 使用 in  的时候,尤其注意一个值 Null  ! 可能在 in (select ID from XXX) 的时候感知不明显,然而在 not in (select ID from XXX) 的时候!假如 ID 有一个是 Null ~恭喜你~整个结果集都不会返回值啦~~这就是任何值和Null比对都会是unknow 的结果撸~~╮(╯_╰)╭ ~知道会出问题才要小心哦~

4、 效率与性能 ——很多文章都有说这个,在XXX地方用exists 比较好,在ZZZ地方用 in 比较好~我的观点是。即使是 XXX 的场景,也有 in 比 exists 效果好的地方!!一切要看实际哦~一定要测试~

额~大家应该都上班了~大家洗楼愉快~