先搞个测试表
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 效果好的地方!!一切要看实际哦~一定要测试~
额~大家应该都上班了~大家洗楼愉快~