IFNULL & COALESCE
In this page:
IFNULL(expression, replacement_value)
COALESCE(expression1, expression2, ..., default_value)The IFNULL Function
The IFNULL function takes two arguments and is MySQL-specific. If the first argument is NULL, it returns the second argument as a fallback default, otherwise it returns the first argument unchanged.
Example: The IFNULL Function
CREATE TABLE users (id INT, nickname TEXT);
INSERT INTO users VALUES (1, NULL), (2, 'Champ');
SELECT id, IFNULL(nickname, 'Guest') AS display_name FROM users;The COALESCE Function Basics
The COALESCE function accepts two or more arguments and is part of the SQL standard. It returns the very first non-NULL value in the list, checking them left to right until it finds one.
Example: The COALESCE Function Basics
CREATE TABLE users (id INT, nickname TEXT, username TEXT, email TEXT);
INSERT INTO users VALUES (1, NULL, NULL, '[email protected]'), (2, 'Champ', 'c123', '[email protected]');
SELECT id, COALESCE(nickname, username, email) AS display_name FROM users;IFNULL vs COALESCE Differences
IFNULL is specific to MySQL and accepts exactly two parameters, making it slightly more concise for the common two-value case. COALESCE is standard SQL and handles many parameters, so it's more portable across different database systems.
Example: IFNULL vs COALESCE Differences
SELECT IFNULL(NULL, 'fallback') AS ifnull_result;
SELECT COALESCE(NULL, NULL, 'third option', 'fourth') AS coalesce_result;Nested IFNULL Expressions
You can nest multiple IFNULL functions to check more than two values in sequence. However, using COALESCE is usually cleaner for three or more fallbacks, since it avoids the deeply nested parentheses that repeated IFNULL calls require.
Example: Nested IFNULL Expressions
CREATE TABLE users (id INT, nickname TEXT, username TEXT);
INSERT INTO users VALUES (1, NULL, NULL);
SELECT id, IFNULL(nickname, IFNULL(username, 'Guest')) AS via_ifnull FROM users;
SELECT id, COALESCE(nickname, username, 'Guest') AS via_coalesce FROM users;Practical Uses in Math Calculations
Math calculations involving NULL will output NULL for the entire expression, even if only one operand is missing. Use IFNULL or COALESCE to ensure math calculations run safely by substituting a default value like zero first.
Example: Practical Uses in Math Calculations
CREATE TABLE items (id INT, price INT, discount INT);
INSERT INTO items VALUES (1, 100, NULL), (2, 50, 10);
SELECT id, price - IFNULL(discount, 0) AS final_price FROM items;- Using
IFNULLwith more than two arguments, when it takes exactly two (useCOALESCE). - Expecting
IFNULL(col, 0)to replace an empty string'', when it only replacesNULL. - Mixing types, like
IFNULL(price, 'N/A'), so the result type changes.
- Arithmetic, comparison, and logical operators build expressions in queries.
- The
CASEexpression adds conditional logic inside a query. IFNULLandCOALESCEprovide fallback values when data isNULL.
Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: