SQL查询LEFT JOIN加IS NOT NULL仍未过滤NULL值问题咨询

问题原因 #

• 你对LEFT JOIN的特性理解有误：你在stu表的关联条件中加了stu.converted_unit is not null，只是限制了参与关联的stu表行必须满足该条件，但LEFT JOIN本身会保留左表的所有记录，当没有符合关联条件的stu行时，stu表的所有字段都会返回NULL，因此最终结果里的alternative unit还是会出现空值。
• 你的CASE表达式没有写ELSE分支：如果四个WHEN判断都不满足，CASE会默认返回NULL，即便stu.converted_unit非空，也可能出现alternative unit price为空的情况。
• 额外说明：你对stockm表使用了LEFT JOIN，但WHERE条件中写了stockm.product = 'M-47-68-BR-02-XX'，这会自动把stockm的左连接转为内连接，只有匹配到stockm的记录才会保留。

解决方案 #

1. 把stu表的LEFT JOIN改为INNER JOIN，这样只有匹配到符合条件的stu行的记录才会返回，直接过滤掉空值，优化后的代码如下：

SELECT DISTINCT
CASE 
    when oplistm.unit_code = 'CASE'  then ROUND(oplistm.price / conv_factor,2)
    when oplistm.unit_code = 'KG' then ROUND(oplistm.price * conv_factor,2)
    WHEN oplistm.unit_code = 'EACH' and stu.converted_unit = 'KG' then ROUND(oplistm.price / conv_factor,2)
    WHEN oplistm.unit_code = 'EACH' and stu.converted_unit = 'CASE' then ROUND(oplistm.price * conv_factor,2)
    ELSE 0 -- 可按需调整默认值，避免返回NULL
end as 'alternative unit price',
stu.converted_unit as 'alternative unit'
FROM sys030.scheme.oplistm oplistm (nolock) 
INNER join sys030.scheme.stockm stockm (nolock) on stockm.product = oplistm.product_code
INNER join sys030.scheme.stunitpm as stu (nolock) on stu.product = oplistm.product_code  and stockm.warehouse = stu.warehouse and stu.base_name = stockm.unit_code and stu.converted_unit is not null
WHERE oplistm.product_code <>'LIC' and oplistm.product_code <> ''  and stockm.product = 'M-47-68-BR-02-XX'

2. 如果你需要保留左表的所有记录，只是想过滤掉stu.converted_unit为空的行，也可以不改关联类型，直接在WHERE条件中新增stu.converted_unit is not null即可。
3. 给CASE表达式补充ELSE分支，根据业务逻辑设置默认值，避免alternative unit price出现空值。

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