MySQL NULL Values: Comparisons, Sorting, and Functions

In MySQL, NULL means a value is unknown or missing. It is different from zero (0) and from an empty string (''). A column can allow or reject NULL values with its NULL or NOT NULL definition.

Compare values with NULL

Ordinary comparisons that involve NULL evaluate to NULL (unknown), not to TRUE or FALSE. This includes NULL = NULL, so a WHERE column = NULL condition does not find rows with a null value.

SELECT
    NULL = NULL AS ordinary_equality,
    NULL <=> NULL AS null_safe_equality,
    NULL IS NULL AS is_null;

+-------------------+--------------------+---------+
| ordinary_equality | null_safe_equality | is_null |
+-------------------+--------------------+---------+
|              NULL |                  1 |       1 |
+-------------------+--------------------+---------+

Use IS NULL or IS NOT NULL to filter for missing or present values. MySQL’s <=> operator is null-safe equality: it returns 1 when both operands are NULL and 0 when only one is NULL.

NULL in sorting, grouping, and aggregates

• In ascending ORDER BY, MySQL sorts NULL values first; in descending order, it sorts them last.
• GROUP BY treats NULL values as equal and places them in the same group.
• Aggregate functions such as SUM() and AVG() ignore NULL inputs. COUNT(column) counts non-NULL values, while COUNT(*) counts rows.
• A MySQL UNIQUE index treats NULL values as distinct by default, so it permits multiple NULL values. See the MySQL unique index guide.

These rules can affect the result even when no query syntax error occurs. For example, WHERE amount <> 0 does not include rows where amount is NULL; add OR amount IS NULL if those rows should be included.

Replace or compare missing values

Use a null-handling function when a query needs a substitute or fallback value:

SELECT IFNULL(phone, 'N/A') AS phone_display;
SELECT COALESCE(phone, email, 'N/A') AS contact;
SELECT NULLIF('', '') AS empty_as_null;

• IFNULL(value, replacement) returns the replacement when value is NULL.
• COALESCE(value1, value2, ...) returns the first non-NULL argument.
• NULLIF(value1, value2) returns NULL when the two arguments are equal; otherwise it returns value1.

For the complete COALESCE() reference, see the MySQL COALESCE() function page. This tutorial also covers the common IFNULL() and NULLIF() patterns above.

For the comparison operators, see MySQL’s official comparison operator documentation. For additional examples of finding NULL values, see the MySQL IS NULL tutorial.