SQL provides several functions to handle NULL values in a more controlled way. A NULL value can be problematic in calculations or when displaying data. These functions allow you to substitute a NULL with another value.
The exact function names vary between different database systems.
IFNULL() and COALESCE()IFNULL(expression, alt_value): Returns alt_value if expression is NULL. Otherwise, it returns expression.COALESCE(value1, value2, ..., alt_value): Returns the first non-NULL value in the list.ISNULL() and COALESCE()ISNULL(expression, alt_value): Similar to MySQL's IFNULL().COALESCE(...): Works the same as in MySQL.NVL() and COALESCE()NVL(expression, alt_value): Similar to IFNULL() and ISNULL().COALESCE(...): Works the same as in other systems.Note: The
COALESCE()function is part of the SQL standard and is generally preferred for portability across different database systems.
Customers Table: (Assume some contacts are NULL)
| CustomerID | CustomerName | ContactName |
|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders |
| 2 | Ana Trujillo Emparedados | NULL |
| 3 | Antonio Moreno Taquería | Antonio Moreno |
| 4 | Around the Horn | NULL |
IFNULL() Example (MySQL)The following SQL statement will return the text "No Contact" if the ContactName value is NULL.
SELECT CustomerName, IFNULL(ContactName, 'No Contact') AS Contact FROM Customers;
Without IFNULL(), the result would show NULL for customers without a contact name.
COALESCE() Example (Standard SQL)The COALESCE() function provides the same functionality and is more portable.
SELECT CustomerName, COALESCE(ContactName, 'No Contact') AS Contact FROM Customers;