MySQL (mariadb) strange VARCHAR WHERE NULL behaviour

Question

I have a table like:

id    name (varchar)
--------------------------
1     test
2     some
3     NULL
4     test

when I query

SELECT * FROM table WHERE name != 'some';

I get the result:

id    name
--------------------------
1     test
4     test

Why it doesn't return the rows with name == NULL ?

Server Version: 5.5.31-MariaDB-1~squeeze-log - mariadb.org binary distribution (protocol version 10)

Client Version: libmysql - 5.1.66 (mysqli) Protokoll-Version: 10

Answer 1

Because comparing with NULL results in UNKNOWN. You have to use the IS operator.

SELECT * FROM table
WHERE name != 'some' 
OR    name IS NULL

Answer 2

For NULL, you need to use WHERE name IS NULL instead of equating it using ==

You can find more details on the MySQL documentation ( 3.3.4.6. Working with NULL Values )

Searching stackoverflow for "MySQL NULL" yields many identical questions

Answer 3

Because NULL is not equivalent with Empty String. NULL simply does not exist or unknown while Empty String exist but a zero-length value. If you want to search including NULL. then you can specify it in the condition.

SELECT * FROM table WHERE name != 'some' OR name IS NULL