查找美国无销量产品组的两种SQL写法正确性咨询

结论 #

两种写法均存在逻辑错误，无法满足需求，具体问题分析与修正方案如下：

需求明确 #

返回美国地区归属的product group中，组内所有item id都没有产生过销售记录的product group。

第一种写法问题分析 #

with cte as
(
select c.*,o.order_id,
case when o.order_id is not null then 1 else 0 end sale_ind 
from Catalog c
left join Orders o
on c.item_id = o.item_id 
and c.location = 'US'
)
select product_group 
from cte 
group by product_group having sum(sale_ind) = 0

核心问题：c.location = 'US'写在了左连接的ON子句中，该条件仅作用于连接匹配逻辑，不会过滤Catalog表的全量数据，最终CTE会包含所有地区的Catalog记录，非美国地区的product group的sale_ind也会被统计为0，会被错误纳入最终结果。

修正方案

将地区过滤条件从ON子句移到WHERE子句，先筛选出美国地区的Catalog记录再做连接，修改后逻辑可用：

with cte as
(
select c.*,o.order_id,
case when o.order_id is not null then 1 else 0 end sale_ind 
from Catalog c
left join Orders o
on c.item_id = o.item_id 
where c.location = 'US' -- 先过滤美国地区的商品目录
)
select product_group 
from cte 
group by product_group having sum(sale_ind) = 0

第二种写法问题分析 #

select c.* 
from Catalog c 
where c.location='US' 
and item_id not in (
   select item_id 
   from Orders)

核心问题有3个：

1. 返回结果是符合条件的Catalog全字段行，不是要求的去重product group；
2. 仅能筛选出美国地区没有订单的单个item，无法保证对应product group下所有item都没有订单：比如某product group下有A、B两个美国item，A有订单、B没有，该写法会把B对应的行返回，但该product group实际有销量，不符合要求；
3. 存在隐性bug：如果Orders表的item_id存在NULL值，NOT IN查询会直接返回空结果，逻辑完全失效。

更稳妥的推荐写法 #

使用NOT EXISTS逻辑规避NOT IN的NULL值问题，逻辑更直观：

SELECT DISTINCT c1.product_group
FROM Catalog c1
WHERE c1.location = 'US'
AND NOT EXISTS (
    SELECT 1
    FROM Catalog c2
    JOIN Orders o ON c2.item_id = o.item_id
    WHERE c2.product_group = c1.product_group
      AND c2.location = 'US'
)

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