SQL Left Join空值处理：多产品发票排除空值，仅空值时保留

核心规则拆解 #

基于Left Join的关联结果，按Invoice维度做分层判定过滤：

• 先统计每张发票关联出的非空产品总数量
• 若某发票的非空产品数≥2：仅保留该发票下产品非空的记录，剔除所有带NULL值的行
• 若某发票的非空产品数=0（即发票完全未匹配到有效产品，所有关联行的产品值均为NULL）：完整保留对应的NULL值记录

参考实现 #

支持窗口函数+Qualify语法的数据库（Hive/Snowflake/BigQuery等） #

直接在原查询基础上增加窗口统计与行过滤规则即可，无需多次关联数据表：

Select 
  a.Invoice,
  b.Prodict
From table1 a
left join table2 b
  on a.Id = b.Id
-- 按发票维度统计非空产品总数，基于统计值过滤行
qualify 
  count(b.Prodict) over(partition by a.Invoice) = 0
  or (count(b.Prodict) over(partition by a.Invoice) >=2 and b.Prodict is not null)

说明：count(b.Prodict)会自动忽略NULL值，计算结果刚好对应每张发票的非空产品总数，无需额外编写复杂的条件判断逻辑。

不支持Qualify语法的数据库（如MySQL 8.0以下版本） #

通过派生表先计算每张发票的非空产品数，再在外层做规则过滤：

SELECT
  Invoice,
  Prodict
FROM (
  Select 
    a.Invoice,
    b.Prodict,
    count(b.Prodict) over(partition by a.Invoice) as non_null_product_cnt
  From table1 a
  left join table2 b
    on a.Id = b.Id
) base
WHERE 
  -- 对应2个及以上产品的发票，仅保留非空产品行
  (non_null_product_cnt >= 2 AND Prodict IS NOT NULL)
  -- 对应纯NULL的发票，保留原NULL行
  OR non_null_product_cnt = 0

灵活调整说明 #

如果实际业务中遇到「单发票仅匹配1个产品、同时附带NULL行」的场景，可根据实际需求调整计数阈值：

• 需要只要有非空产品就剔除同发票NULL值：把判定条件中的>=2改成>=1即可
• 需要保留单产品场景下的NULL行：维持原>=2的判定阈值即可

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