NULL values in where clause

Question

i've got a table "bla" like this:

[id]    [name]    [fk]
1       test      4
2       foo       5
3       bar       NULL

if i do the sql query

SELECT * FROM bla WHERE fk <> 4

i only get the record with the id 2. i don't get the record with id 3 where fk is null. I thought NULL != 4. Seems that this is wrong.

Why is this so?

Answer 1

How about

SELECT * FROM bla WHERE NOT (fk = 4)

Logic:

NULL = 4 --false
5 = 4    --false
4 = 4    --true

NOT (NULL = 4) --true
NOT (5 = 4)    --true
NOT (4 = 4)    --false

Answer 2

NULL is special in that it represents an "unknown" value. This can't be compared to numbers (or any other value for that matter), hence the result -

Is NULL <> 4? The answer is - don't know. Is 4 different from an unknown value?

Try this instead:

SELECT * FROM bla WHERE fk <> 4 OR FK IS NULL

Answer 3

NULL doesn't compare equal to anything. You'll need to accept nulls explicitly:

where fk <> 4 or fk is null;

See Working with NULL for more information about NULL handling.

Answer 4

Because NULL stands for UNKNOWN, and when you compare a value with UNKNOWN, the result will always be false.

Take a look at this comparisons -

NULL = NULL    -- false, since both are unknown, so the truth value of this expression can't be determined.
NULL = 4       -- false
4 = 4          -- true, since both values are known.

If you want to fetch the records containing NULL, you need to re-write your query this way -

where fk <> 4
OR fk is null;

For more information, see Wikipedia.

Answer 5

NULL is not a value, but rather the unknown absence of a value. If you'd like to test for NULL, you have to do so explicitly by using IS NULL and IS NOT NULL. For example, NULL will test FALSE even against NULL itself. So, working with NULL is only done with the aforementioned functions (and ISNULL()). Your query could be rewritten as

SELECT * FROM bla WHERE fk <> 4 OR fk IS NULL

Answer 6

Following statement should help:

SELECT * FROM bla WHERE COALESCE(fk,0) <> 4