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.
• Operations with NULL return NULL.
• Comparing NULL = NULL returns FALSE.

Working with NULL

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

MySQL 8.1
-- 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.1
  COALESCE(val1[, val2, ...., val_n])
  

• ISNULL

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

  MySQL 8.1
  ISNULL(value)
  

• IFNULL

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

  MySQL 8.1
  IFNULL(value, alternative_value)