今天说一下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 效果好的地方！！一切要看实际哦~一定要测试~

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

相关文章