SQL <> false not returning fields with null-CodePudding

I am setting a field based on another field from a table with this check:

count(case
          when recipient_status = 'Premium' and
                    delivered_in_estimated_time_window <> false
              then 1 end)

If I get a value delivered_in_estimated_time_window as null I would like to count it, and only if the value is false I don't want to count it. So, for example if a row looks like this:

recipient_status | delivered_in_estimated_time_window
'Premium'        | null

Then I would like add to count 1. Only if a row looks like this one below I don't want to count it:

recipient_status | delivered_in_estimated_time_window
'Premium'        | false

But, this seems to not count null fields. Why is <> false not working for null values?

CodePudding user response：

NULL is not true or false, it's nothing, therefore you can't check if it's true or false. If you want that NULL values will behave like true, you can use COALESCE to replace NULL values by true:

...COALESCE(delivered_in_estimated_time_window,true) <> false...

So, your sample will be:

COUNT(CASE
      WHEN recipient_status = 'Premium' AND
                COALESCE(delivered_in_estimated_time_window,true) <> false
          THEN 1 END)

CodePudding user response：

COUNT(
    CASE WHEN recipient_status='Premium' AND
              IFNULL(delivered_in_estimated_time_window, true) <> false 
         THEN 1 
    END
)

As stated above, NULL is 'special': it isn't equal or unequal to anything else, it is just NULL. Wrap things in IFNULL or COALESCE or add more conditions including IS (NOT) NULL as suggested above.