NULLIF
Compares two values and returns NULL if they are equal.
MySQL 8.1
NULLIF(value_1, value_2)Parameters
- value_1
- Value returned if the arguments are not equal
- value_2
- Value to compare with value_1
Return value
NULL if the arguments are equal, otherwise value_1
Examples
Equal values give NULL
MySQL 8.1
SELECT NULLIF('SQL Academy', 'SQL Academy');Different values give the first
MySQL 8.1
SELECT NULLIF('SQL', 'Academy');Empty promo codes are not counted
MySQL 8.1
SELECT COUNT(promo_code) AS filled,
COUNT(NULLIF(TRIM(promo_code), '')) AS with_code
FROM orders;Division without dividing by zero
MySQL 8.1
SELECT session_id,
pages_viewed,
duration_sec,
ROUND(pages_viewed * 60 / NULLIF(duration_sec, 0), 1) AS pages_per_minute
FROM sessions
WHERE duration_sec < 20
ORDER BY session_id
LIMIT 5;Details
NULLIF(a, b) is a short form of CASE WHEN a = b THEN NULL ELSE a END. It is most often used to turn an «empty» value into NULL, so that COUNT, AVG and COALESCE skip it.
The second common task is division: x / NULLIF(y, 0) returns NULL instead of dividing by zero. In MySQL, division by 0 gives NULL anyway, while in PostgreSQL it is an error, so the form with NULLIF works the same in both DBMSs.