COALESCE / NULLIF

Returns the first non-NULL value (standard SQL).

SELECT COALESCE(value1, value2, value3, ...);

Returns NULL if the two values are equal; otherwise returns value1 (standard SQL).

SELECT NULLIF(value1, value2);

Returns the fallback value if the value is NULL (MySQL-specific).

SELECT IFNULL(value, default_value);

The following examples use the members table.

Displays "Not registered" when a phone number is NULL.

sample_coalesce_nullif.sql

SELECT name,
       COALESCE(phone, 'Not registered') AS phone
FROM members;

+--------+----------------+
| name   | phone          |
+--------+----------------+
| user_a | 090-1234-5678  |
| user_c | Not registered |
| user_e | 03-9876-5432   |
| user_b | Not registered |
+--------+----------------+
4 rows in set

Displays the nickname if available, otherwise the full name.

sample_coalesce_nullif.sql

SELECT COALESCE(nickname, name) AS display_name
FROM members;

+--------------+
| display_name |
+--------------+
| user_a       |
| nickname_c    |
| user_e       |
| user_b       |
+--------------+
4 rows in set

Returns NULL when the nickname is an empty string, treating it as not set.

sample_coalesce_nullif.sql

SELECT name,
       NULLIF(nickname, '') AS nickname
FROM members;

+--------+-----------+
| name   | nickname  |
+--------+-----------+
| user_a | NULL      |
| user_c | nickname_c |
| user_e | NULL      |
| user_b | NULL      |
+--------+-----------+
4 rows in set

Uses IFNULL to display "Not set" when a nickname is NULL (MySQL).

sample_coalesce_nullif.sql

SELECT name,
       IFNULL(nickname, 'Not set') AS nickname
FROM members;

+--------+-----------+
| name   | nickname  |
+--------+-----------+
| user_a | Not set   |
| user_c | nickname_c |
| user_e | Not set   |
| user_b | Not set   |
+--------+-----------+
4 rows in set

Both COALESCE and NULLIF are defined in standard SQL and can be used across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

SELECT COALESCE(phone, 'Not registered') AS phone FROM members;
SELECT NULLIF(nickname, '') AS nickname FROM members;

The shorthand NULL-replacement syntax varies by database. MySQL uses IFNULL, Oracle uses NVL, and SQL Server uses ISNULL.

-- MySQL: uses IFNULL.
SELECT name, IFNULL(nickname, 'Not set') AS nickname FROM members;

-- Oracle: uses NVL.
SELECT name, NVL(nickname, 'Not set') AS nickname FROM members;

-- SQL Server: uses ISNULL.
SELECT name, ISNULL(nickname, 'Not set') AS nickname FROM members;

For portability, it is recommended to use the standard SQL COALESCE rather than database-specific functions.

COALESCE is defined in standard SQL and works in both MySQL and PostgreSQL. Because you can pass three or more arguments, you can express prioritized fallback logic in a single line — for example, "use the nickname if available, otherwise the full name, otherwise 'Anonymous'."

NULLIF is primarily used to avoid division-by-zero errors. For example, writing total / NULLIF(count, 0) causes the expression to return NULL instead of an error when count is 0.

To check for the presence of NULL, see IS NULL / IS NOT NULL. To switch values based on conditions, see CASE.