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, fallback);

-- Displays "Not registered" when a phone number is NULL.
SELECT name,
       COALESCE(phone, 'Not registered') AS phone
FROM members;

-- Displays the nickname if available, otherwise the full name.
SELECT COALESCE(nickname, name) AS display_name
FROM members;

-- Returns NULL when discount is 0, excluding those rows from the average.
SELECT AVG(NULLIF(discount, 0)) AS avg_nonzero_discount
FROM orders;

-- Avoids division by zero when calculating the unit price.
SELECT product_name,
       total_sales / NULLIF(quantity, 0) AS unit_price
FROM products;

-- Uses IFNULL to treat NULL points as 0 (MySQL).
SELECT name,
       IFNULL(points, 0) AS points
FROM members;

-- Example result of: SELECT name, COALESCE(phone, 'Not registered') AS phone FROM members;
-- +----------+----------------+
-- | name     | phone          |
-- +----------+----------------+
-- | Tanaka   | 090-1234-5678  |
-- | Suzuki   | Not registered |
-- | Sato     | 03-9876-5432   |
-- +----------+----------------+

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

-- Syntax that works across all databases.
SELECT COALESCE(phone, 'Not registered') AS phone FROM members;
SELECT total_sales / NULLIF(quantity, 0) AS unit_price FROM products;

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(points, 0) AS points FROM members;

-- Oracle: uses NVL.
SELECT name, NVL(points, 0) AS points FROM members;

-- SQL Server: uses ISNULL.
SELECT name, ISNULL(points, 0) AS points 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.