Dealing with NULL

# Matching NULL SELECT id FROM author WHERE name IS NULL; # Using the NULL-safe equality operator SELECT * FROM some_table WHERE NOT some_column <=> 1; 

NULL is a tricky little bit of SQL syntax that stands in for nothing. Note that nothing is not the same as empty or zero. This distinction leads to a good deal of confusion when dealing with NULL. For example, try running the following snippets of SQL in MySQL:

SELECT NULL = FALSE; SELECT NULL = ''; SELECT NULL = 0; SELECT NULL = NULL; 

The result of all of these queries is NULL. NULL does not have a valueby definition it means there is no valueand it has no data type. It is therefore not equal to a Boolean FALSE, an empty string, or an integer zero. When you compare NULL to another value, however, the result is always NULL, not FALSE or zero. Furthermore, NULL does not equal NULL, as the previous comparison shows.

If you are working with a column that contains NULL values, keep these things in mind:

• If you try to compare anything to NULL using the standard comparison operators, the comparison returns NULL. The practical effect of this is that queries such as the following always return no rows:

  SELECT * FROM some_table WHERE some_column != NULL; 

• Use the special NULL-safe operators IS NULL and <=> to safely compare anything to a NULL.