你真的会玩SQL吗？EXISTS和IN之间的区别-优快云博客

本文详细解释了SQL中EXISTS与IN操作符的区别，尤其是在处理NULL值时的行为差异，并通过具体示例帮助读者理解如何正确选择使用这两个操作符。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

你真的会玩SQL吗？EXISTS和IN之间的区别

EXISTS和IN之间的区别

1.EXISTS只返回TRUE或FALSE，不会返回UNKNOWN。

2.IN当遇到包含NULL的情况，那么就会返回UNKNOWN。

当查询的列包含NULL时，NOT EXISTS正常返回TRUE或FALSE。

而NOT IN可能返回空集,如下

1：val IN(val1,val2,...,NULL)，永远不会返回FALSE，而是返回TRUE或UNKNOWN。

2：val NOT IN(val1,val2,...,NULL)，永远不会返回TRUE，而是返回NOT TRUE或NOT UNKNOWN。

看个示例：

Test1表

select t.[name] from Test as t

where exists (select t1.orderid from Test1 as t1 where t1.[name]=t.[name])

返回 aaa,ccc,ddd

select t.[name] from Test as t

where t.[name] in (select t1.[] from Test1 as t1)

返回 aaa,ccc,ddd

select t.[name] from Test as t

where not exists (select t1.orderid from Test1 as t1 where t1.[name]=t.[name])

返回 bbb

select t.[name] from Test as t

where t.[name] not in (select t1.[name] from Test1 as t1)

返回空集

练习

以下对就返回哪三值？

答案
View Code

用例数据库文件你真的会玩SQL吗？之逻辑查询处理阶段文末

custid      companyname
----------- ----------------------------------------
21          Customer KIDPX
23          Customer WVFAF
33          Customer FVXPQ
36          Customer LVJSO
43          Customer UISOJ
51          Customer PVDZC
85          Customer ENQZT
参考SQL：
--answer:
select custid, companyname
from Sales.Customers as C
where EXISTS
(select *
   from Sales.Orders as O
   where O.custid = C.custid
     and O.orderdate >= '20070101'
     and O.orderdate < '20080101')
and not EXISTS
(select *
   from Sales.Orders as O
   where O.custid = C.custid
     and O.orderdate >= '20080101'
     and O.orderdate < '20090101');