NULL values

In the last four lessons, we have sometimes mentioned the value NULL without going into detail about it. Actually, NULL is not a concrete value either. Wikipedia writes about this:

Null or NULL is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDMS) support a representation of “missing information and inapplicable information”.

A null should not be confused with a value of 0. A null value indicates a lack of a value, which is not the same thing as a value of zero.

Wikipedia, Null (SQL)

So you can think of ZERO as “not defined”. So we can’t make any meaningful comparisons with NULL . The result of a comparison with NULL is always the logical value UNKNOWN.

NULL in field lists

In field lists, NULL can occur in some places. For example, in an OUTER JOIN or if no ELSE value is defined in a CASE expression. We can catch this situation with the following two functions:

  • IFNULL(<value>, <fallback>)– The value is returned if it is not NULL . Otherwise the fallback value is used instead. IFNULL always has exactly two arguments.
  • COALESCE(<value1>, <value2>[,…] ) – This function is equivalent to the IFNULL function with two arguments. But it can also be called with three or even more arguments. The return value is the first value that is not NULL.

NULL in predicates

You can’t make a meaningful comparison with NULL because the result is always UNKNOWN . For example, the comparative predicate logically always results in

WHERE name = NULL 

UNKNOWN. Therefore, you must use the IS NULL predicate to filter the NULL values:

WHERE name IS NULL
-- oder 
WHERE name IS NOT NULL