NOT EXISTS查询未返回预期结果，求SQL语句修正方案

场景与数据 #

表结构与数据 #

orderid     id                number         fulfilledproduct
2           BundleSpec        1              ID
2           TemplateSpec      1              IDSheet
2           TemplateSpec      1              IDCertificate
10          BundleSpec        1              ID
10          TemplateSpec      1              IDSheet

现有查询语句 #

select (orderid),(id),(number),(fulfilledproduct) 
from [Product] 
where 
  id in ('BundleSpec','TemplateSpec') 
  and not exists 
  (
    select (orderid) 
    from [Product] 
    where 
      id in ('BundleSpec','TemplateSpec') 
      and fulfilledproduct = 'IDCertificate'
  )

预期输出 #

orderid         id                number         fulfilledproduct
    10          BundleSpec        1              ID
    10          TemplateSpec      1              IDSheet

问题原因 #

你的NOT EXISTS子查询没有关联外部查询的orderid，它只是检查整个表中是否存在任何一条fulfilledproduct='IDCertificate'的记录。因为表中确实存在orderid=2的这条记录，所以NOT EXISTS的结果为false，导致所有符合id条件的行都被过滤，最终返回0行。

解决方案 #

方案1：修正NOT EXISTS的关联逻辑 #

给外部表起别名，在子查询中关联内外表的orderid，确保只检查当前订单是否存在IDCertificate记录：

select orderid, id, number, fulfilledproduct
from [Product] outer_p
where 
  id in ('BundleSpec','TemplateSpec') 
  and not exists 
  (
    select 1
    from [Product] inner_p
    where 
      inner_p.orderid = outer_p.orderid
      and inner_p.fulfilledproduct = 'IDCertificate'
  )

方案2：使用EXCEPT实现 #

先查询所有符合id条件的记录，再排除那些属于存在IDCertificate的订单的记录：

-- 所有符合id条件的记录
select orderid, id, number, fulfilledproduct
from [Product]
where id in ('BundleSpec','TemplateSpec')

except

-- 存在IDCertificate的订单的所有相关记录
select p.orderid, p.id, p.number, p.fulfilledproduct
from [Product] p
join (
    select distinct orderid
    from [Product]
    where fulfilledproduct = 'IDCertificate'
) cert on p.orderid = cert.orderid
where p.id in ('BundleSpec','TemplateSpec')

两种方案都能得到预期结果，其中修正NOT EXISTS的写法更高效，无需额外的join和distinct操作。

内容的提问来源于stack exchange，提问作者Michele