Drop Shadow

NULL comparisons are always FALSE

In MySQL 4.0, the following query would produce all entries that are not listed as students:

SELECT * FROM people WHERE e-mail = 'person@example.com' AND person_type != 'Student'

In MySQL 5.0, that query will also ignore any entries whose department is NULL.

For example, if there are twenty entries with ‘student’ for person_type, thirty entries with ‘employee’ as person_type, forty entries with ‘guest’ as person_type, and ten entries with nothing at all as person_type (NULL), the above query will return seventy entries. It will not return the twenty ‘student’ person_types, and it will also not return the ten NULL person_types.

This is because, in SQL, any comparison against a NULL is also NULL. If the department contains a NULL value, then “department != 'Student'” is also NULL (or UNKNOWN). For all practical purposes, in a WHERE clause the NULL value is equivalent to a FALSE value. This means that if you are doing comparisons where a field can contain a NULL value, you’ll need to ensure that you either include NULL as an option or you replace all NULL values with something else (such as an empty string, or some value that makes sense to you).

The only comparisons against NULL values that won’t return a NULL are the IS NULL and NOT NULL options, and the built-in NULL-oriented functions such as ISNULL() and IFNULL().