如何在SQL WHERE条件中用CASE语句处理左关联字段空值问题

当然可以实现这个逻辑，不需要用CASE语句，直接通过逻辑运算符组合就能完成需求：

将原WHERE子句中的ah.start_timestamp > bh.start_time替换为：

(bh.start_time IS NULL OR ah.start_timestamp > bh.start_time)

修改后的完整SQL #

select distinct
      ora_hash( ah.target_name 
                   || to_char( start_timestamp, 'DD-MON-YY HH24:MI:SS' ))
         || ','
         || 'Critical'
         || ','
         || host_name
         || ','
         || ah.target_name
         || ','
         || 'Instance unexpectedly shutdown at '
         || to_char( start_timestamp, 'DD-MON-YY HH24:MI:SS' )
   from 
      sysman_ro.mgmt$availability_history ah
         join sysman_ro.mgmt$target_members tm 
            on ah.target_name = tm.member_target_name
         join sysman_ro.mgmt$target mt 
            on ah.target_name = mt.target_name
         left outer join sysman_ro.mgmt$blackout_history bh  
            on mt.target_name = bh.target_name
   where 
          tm.aggregate_target_name like 'PROD_DB'
      and ah.availability_status_code = 0
      and ah.start_timestamp > sysdate - 0.2
      and (bh.start_time IS NULL OR ah.start_timestamp > bh.start_time)
      and ah.target_type = 'oracle_database'

逻辑说明 #

• 当bh.start_time为空时，bh.start_time IS NULL为真，整个括号内的表达式结果为真，相当于跳过该条件的判断
• 当bh.start_time有值时，会进入后半部分的判断，仅当ah.start_timestamp > bh.start_time成立时，该条件才会通过

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