SQL <> false not returning fields with null

Question

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?

Because NULL isn't equal or unequal to anything, not even another NULL. You should check for NULL with `IS (NOT) NULL` — HoneyBadger, Jun 02 '22 at 10:38
Does this answer your question? [SQL is null and = null](https://stackoverflow.com/questions/9581745/sql-is-null-and-null) — HoneyBadger, Jun 02 '22 at 10:44
so I need to have a check like this one: ```delivered_in_estimated_time_window = true or delivered_in_estimated_time_window is null```, is there a more elegant way of doing this check? — Ludwig, Jun 02 '22 at 10:44
You could try `delivered_in_estimated_time_window is distinct from false`. ANSI SQL, but not so commonly supported. — jarlh, Jun 02 '22 at 11:52

score 0 · Answer 1 · edited Jun 02 '22 at 12:11

0

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.

edited Jun 02 '22 at 12:11

derloopkat

6,232
16
38
45

answered Jun 02 '22 at 10:45

Note that IFNULL is a product specific function, and OP hasn't specified the dbms which is used. – jarlh Jun 02 '22 at 10:48

score 0 · Accepted Answer · answered Jun 02 '22 at 10:46

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)

SQL <> false not returning fields with null

2 Answers2