Snowflake中使用<>过滤条件为何会同时过滤NULL值？

背景与代码 #

我们在支付交易表中通过以下CASE语句对记录分类：

case 
  when TYPE = 'PTT.N' then 'Authentication'
  when TYPE = 'PTT.R' then 'Rebill'
  when TYPE = 'PTT.U' then 'Update'
  when TYPE = 'PTT.C' then 'Cancel'
  when TYPE = 'PTT.M' then 'Migration'
  when TYPE is null then 'Order'
  else TYPE
end

通过视图访问生产表时，因业务规则变更需过滤'PTT.M'记录，但执行后发现原TYPE为NULL的记录也被过滤了。视图代码如下：

with source as (
    select 
      ...
    from  payments
    left join payment_service ON payments.PAYMENT_SERVICE = payment_service.ID
),

renamed as (
  SELECT
      ID as payment_id,
      zeroifnull(AMOUNT) AS amount,
      SERVICE_PAYER_IDENTIFIER,
      CREATION_DATE,
      ...
      case 
        when TYPE = 'PTT.N' then 'Authentication'
        when TYPE = 'PTT.R' then 'Rebill'
        when TYPE = 'PTT.U' then 'Update'
        when TYPE = 'PTT.C' then 'Cancel'
        when TYPE = 'PTT.M' then 'Migration'
        when TYPE is null then 'Order'
        else TYPE 
      end as type,
      ...
  from source
)

select * from renamed where type<>'PTT.M';

疑问 #

NULL并非字符串，WHERE子句where type<>'PTT.M'仅做字符串比较，为何NULL值也会被过滤？

补充：已通过修改WHERE子句为ifnull(type, '') <> 'PTT.M'解决问题。

核心原因：SQL的NULL比较规则 #

在SQL逻辑中，NULL代表“未知值”，任何与NULL的比较操作（包括=、<>, >、<等）都会返回UNKNOWN，而非TRUE或FALSE。WHERE子句仅保留判断结果为TRUE的行，UNKNOWN会被视为FALSE处理，因此所有type为NULL的行都会被过滤。

你可能疑惑CASE语句明明处理了NULL，为什么type还会是NULL？通常是因为CASE语句未覆盖所有场景：

• 若source中的TYPE字段存在既不匹配前面的枚举值（如'PTT.N'），也未被when TYPE is null捕获的NULL情况（比如left join后关联表的TYPE为NULL，而你的CASE语句中的TYPE来自关联表），else TYPE会直接返回原NULL值，导致type字段为NULL。

可行的解决方案 #

方案1：你的现有解决方法 #

使用ifnull(type, '') <> 'PTT.M'，将NULL转换为空字符串后再比较，此时空字符串与'PTT.M'的比较结果为TRUE，从而保留这些行。

方案2：明确包含NULL判断 #

直接在WHERE子句中添加NULL的判断逻辑：

select * from renamed where type <> 'PTT.M' OR type IS NULL;

方案3：优化CASE语句避免NULL #

调整CASE语句，确保type字段不会出现NULL值，比如修改else分支：

case 
  when TYPE = 'PTT.N' then 'Authentication'
  when TYPE = 'PTT.R' then 'Rebill'
  when TYPE = 'PTT.U' then 'Update'
  when TYPE = 'PTT.C' then 'Cancel'
  when TYPE = 'PTT.M' then 'Migration'
  when TYPE is null then 'Order'
  else COALESCE(TYPE, 'Order')
end as type

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