IFNULL & COALESCE
In this page:
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;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: