SQL中行为不当的子查询,IN,exists

SQL中行为不当的子查询

IN使用的是三值逻辑(True,False,Unknown),并且用not运算符否定unknown,得到的仍是unknown,而unknown在查询过滤语句时会补过滤掉

exists使用的是二值谓词逻辑,总是返回True,False,而绝不会返回unknown.

  

以下是不当的子查询:

select custid,companyname

from sales.customers as c

where custid not in 

(select o.custid from sales.orders as o);

  

以下较为合理的子查询

select custid,companyname

from sales.customers as c

where custid not in

 (select o.custid from sales.orders as o where o.custid is not null);

  

或者

select custid,companyname

from sales.customers as c

where not exists