NULL values ​​in the where clause

Question

I have a table "bla" like this:

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

if i make sql query

 SELECT * FROM bla WHERE fk <> 4 

I only get the record with id 2. I don't get the record with id 3, where fk is null. I thought NULL! = 4. This seems to be wrong.

Why is this so?

Answer 1

NULL does not compare with anything. You must explicitly accept NULL values:

 where fk <> 4 or fk is null; 

See Working with NULL for more information on handling NULL .

Answer 2

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

Take a look at these 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 get records containing NULL , you need to re-write the query this way -

 where fk <> 4 OR fk is null; 

For more information, see Wikipedia .

Answer 3

NULL differs in that it represents an "unknown" value. This cannot be compared with numbers (or any other value in this regard), therefore, the result is

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

Try this instead:

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

Answer 4

NULL is not a value, but rather an unknown lack of value. If you want to test NULL, you must do this explicitly using IS NULL and IS NOT NULL . For example, NULL will check FALSE even against NULL itself. Thus, working with NULL is performed only with the above functions (and ISNULL() ). Your request can be rewritten as

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

Answer 5

What about

 SELECT * FROM bla WHERE NOT (fk = 4) 

Logics:

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

Answer 6

The following statement should help:

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