Can anyone tell me why my case statement return null(good)

Question

I have a query return null(good) with case statements,but when use where statement for each case, have records in output .

select a,b,sysdate ,      
 CASE 
     WHEN a IS NULL  AND SYSDATE > b THEN 'O'
     WHEN a IS NULL AND SYSDATE <= b THEN 'W'
     WHEN a > b THEN 'Fail'
     WHEN a <= b THEN 'Pass'
   else 'good'
 END  as result
 from mytable

a | b | sysdate | result

null | null| 10-JUL-19 |good

null | null| 10-JUL-19 |good

null | null| 10-JUL-19 |good

null | null| 10-JUL-19 |good

I have ouput when I execute this query(or other two)

select a,b,sysdate  from mytable 
where a > b

output(where query):

a | b | sysdate

10-APR-15| 06-APR-15| 10-JUL-19|

06-APR-15| 06-APR-15| 10-JUL-19|

02-APR-15| 01-APR-15| 10-JUL-19|

select a,b,sysdate  from mytable 
  where a IS NULL  AND SYSDATE > b 

select a,b,sysdate from mytable
   where a <= b

You can't compare some values with null values in SQL server, you need to have some values or do some isnull condition to avoid nulls — Avi, Jul 11 '19 at 03:43
You get "good" because `b` is `NULL` and `a` is `NULL`. So, none of the `when` conditions evaluates to `true`. — Gordon Linoff, Jul 11 '19 at 12:29

score 0 · Accepted Answer · answered Jul 11 '19 at 05:24

When where statement gets null values, then it returns unknown, which ecludes record from resultset.

Read more about three valued logic in SQL Server (just google it :) ).

This is why you don't get any records. For example, comapring to a column, where a is always null, gives you no records in rsult.

You need to use coalesce or similair "null-preventing" functions.

Can anyone tell me why my case statement return null(good)

1 Answers1