Why is "AND NOT field = 'value'" filtering out nulls?

Question

My query looks basically like

WITH DATA AS(
 SELECT fields
    FROM table
        WHERE [many conditions]
        AND NOT field1 = 'string'           
)

SELECT foo,
    bar,
    CASE
        WHEN field1 IS NULL THEN 'other_string'
        [other cases]
    END
FROM data

There's plenty of other stuff going on, but this is the core bit that isn't working. This should give me plenty of results. but instead, that particular case has zero results; if i cut out the rest of the query and run basically just this, it's just an empty table.

AND NOT field1 = 'string' seems to be filtering out all rows where field1 is 'string', but ALSO all rows where field1 is null. If field1 is null, then field1 = 'string' should evaluate false (since null doesn't equal anything) and therefore NOT field1 = 'string' should evaluate true and those rows should be in the query—right?

Not actually sure what sql implementation is running behind the Redash frontend I'm using but I can find out if it's relevant.

You have WITH `DATA` (uppercase), but your SELECT is from `data` (lowercase). Can you confirm this isn't a case-insensitive issue causing zero results? Basically does `SELECT * from DATA LIMIT 1` work? — tester, Feb 04 '21 at 17:58
@tester yes, changing the case doesn't do anything (and the query works otherwise even with the mismatch). — Skaramuche, Feb 04 '21 at 17:59
@GordonLinoff I don't understand your comment; there isn't any NOT NULL in this query as far as I know. there's something that evaluates to NOT FALSE. is there some reason it could end up as NOT NULL instead? — Skaramuche, Feb 04 '21 at 18:01
Already tried changing to != i'm afraid, didn't make any difference — Skaramuche, Feb 04 '21 at 18:01

score 2 · Accepted Answer · answered Feb 04 '21 at 20:58

Almost any comparison with NULL returns NULL. Two prominent exceptions are IS NULL and IS NOT NULL, which return either "true" or "false".

NULL has pretty simple semantics:

NOT NULL --> NULL
TRUE AND NULL --> NULL
FALSE AND NULL --> FALSE
TRUE OR NULL --> TRUE
FALSE OR NULL --> NULL

The semantics are easier to follow if you think of NULL as representing an *unknown" value rather than a missing value.

A WHERE clause only passes through rows that evaluate to "true". So, both "false" and NULL are removed. Note that this is the same for CASE expressions. But, CHECK expressions treat NULL the same as "true". Well, no one ever accused SQL of internal consistency.

Your expression is:

WHERE [many conditions] AND NOT field1 = 'string'

When field1 is NULL this is:

WHERE [many conditions] AND NOT (NULL = 'string')
WHERE [many conditions] AND NOT (NULL)
WHERE [many conditions] AND NULL
WHERE NULL

Very simple logic.

Note: Standard SQL has a NULL safe comparator, which would be:

WHERE [many conditions] AND field1 IS DISTINCT FROM 'string'

Some databases use <=> for this purpose:

WHERE [many conditions] AND NOT field1 <=>'string'

And in others you need to be more explicit:

WHERE [many conditions] AND (field1 <> 'string' OR field1 IS NULL)

like, it also makes me mad, because i don't understand WHY SQL would say that 'string' = NULL would be NULL instead of just being false, since clearly a null isn't meaningfully "equal" to any non-null value. But at least I know how it works now. — Skaramuche, Feb 04 '21 at 22:42

Pascal · Answer 2 · 2021-02-04T21:20:15.753

1

If you compare NULL with another value in SQL the result will be NULL. But when you compare explicitly using IS NULL or IS NOT NULL the NULL values will be considered accordingly.

So your condition should probably be

(NOT (field1 = 'string') OR field1 IS NULL)

Or maybe a bit more readable (<> means not equals):

(field1 <> 'string' OR field1 IS NULL)

Here you can find some details about SQL NULL values.

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

edited Feb 04 '21 at 21:20

answered Feb 04 '21 at 18:20

Pascal

122
6

"If you compare NULL with another value in SQL the result will always be false" —> that was my original understanding, which is why I wrote my query the way I did. if this were true, then field1 = 'string' would evaluate to false and my query would work. Your answer implies that if you compare NULL to another value, something else happens instead, not it being FALSE. clearly it isn't TRUE. surely those should be the only two possible results?... – Skaramuche Feb 04 '21 at 18:40
Ok, my sentence you quoted may be misleading. The problem is that `NOT field1 = 'string'` is evaluated as one condition. You are assuming, that `field1 = 'string'` ist evaluated first and then inverted by the leading NOT. But it does the same as `field1 <> 'string'`. I'm not shure what happens if you write `NOT (field1 = 'string')`. But even that may be false for NULL. Short story: just test for NULL explicitly whenever you expect the value can be NULL. – Pascal Feb 04 '21 at 20:28
I googled just now for 'sql result of comparison with null'. First result seems to deal with your question or misunderstanding in depth: [https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/](https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/) – Pascal Feb 04 '21 at 20:33

Why is "AND NOT field = 'value'" filtering out nulls?

2 Answers2