What is NULL and How to Work with It in SQL?

NULL is a special value in SQL that represents the absence of data or an unknown value.

Characteristics of NULL:

• NULL is not equivalent to an empty string or zero.
• Arithmetic, comparisons, and most functions return NULL when one of the operands is NULL: 5 + NULL is NULL.
• Comparing NULL = NULL also returns NULL, not true: it is unknown whether two unknown values are equal. That is why WHERE field = NULL finds no rows — use IS NULL instead.
• Logical operators are an exception: NULL OR true returns true, and NULL AND false returns false.

Working with NULL

To check for NULL, use the IS NULL or IS NOT NULL operator.

MySQL 8.4
-- Finding records with an unknown birth date
SELECT * FROM employees WHERE birth_date IS NULL;

-- Finding records with a known birth date
SELECT * FROM employees WHERE birth_date IS NOT NULL;

Functions for working with NULL

• COALESCE

  Returns the first non-NULL value from the list.

  MySQL 8.4
  COALESCE(val1[, val2, ...., val_n])
  

• ISNULL

  Returns 1 or 0 depending on whether the expression is NULL.

  MySQL 8.4
  ISNULL(value)
  

• IFNULL

  Returns the first argument if it is not NULL. Otherwise, it returns the second argument.

  MySQL 8.4
  IFNULL(value, alternative_value)
  

For a deeper dive with examples, see the article “SQL COALESCE: Replacing NULL and the NULLIF Combo”.