MySQL IFNULL() and COALESCE()

IFNULL() and COALESCE() let a query substitute a value when data is NULL. IFNULL(expr1, expr2) accepts two expressions; COALESCE() can test a list and returns the first non-NULL expression.

IFNULL()

SELECT id, name, IFNULL(class, 'not known') AS class, mark
FROM student3;

Output:

The replacement affects the query result only; it does not update the stored value.

SELECT id, name, class, IFNULL(mark, 1) * 2 AS doubled_mark
FROM student3;

SELECT id, name, class, 100 / IFNULL(mark, 1) AS score_ratio, mark
FROM student3;

COALESCE()

Sample customer data:

SELECT id, name, class, 100 / COALESCE(mark, 1) AS score_ratio, mark
FROM student3;

SELECT COALESCE(first_name, last_name) AS full_name
FROM customer;

When several columns can contain a value, COALESCE() returns the first non-NULL item in the list.

SELECT id, COALESCE(first_name, middle_name, last_name) AS name
FROM customer;

Output:

Aggregate functions and NULL

Aggregate functions such as MAX(), MIN() and COUNT(column) ignore NULL values. COUNT(*) counts rows regardless of whether individual columns contain NULL.

SELECT MAX(mark) FROM student3;

SELECT MIN(mark) FROM student3;

SELECT COUNT(*) FROM student3;

SELECT COUNT(mark) FROM student3;

Download student3 SQL dump

Customer sample table

CREATE TABLE customer (
  id INT NOT NULL,
  first_name VARCHAR(10) DEFAULT NULL,
  middle_name VARCHAR(10) DEFAULT NULL,
  last_name VARCHAR(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO customer (id, first_name, middle_name, last_name) VALUES
(1, NULL, 'King', NULL),
(2, NULL, NULL, 'Queen'),
(3, 'Jack', NULL, NULL),
(4, NULL, NULL, NULL),
(5, 'Arnold', 'K', 'St'),
(6, NULL, NULL, 'Ravi');

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: