MySQL COALESCE Function
Summary: this tutorial introduces you to the MySQL COALESCE function that allows you to substitute NULL values.
Introduction to MySQL COALESCE function
The following illustrates the COALESCE
function syntax:
COALESCE(value1,value2,...);
Code language: SQL (Structured Query Language) (sql)
The COALESCE
function takes a number of arguments and returns the first non-NULL argument. In case all arguments are NULL, the COALESCE
function returns NULL.
The following shows some simple examples of using the COALESCE
function:
SELECT COALESCE(NULL, 0); -- 0
SELECT COALESCE(NULL, NULL); -- NULL;
Code language: SQL (Structured Query Language) (sql)
MySQL COALESCE function examples
See the following customers
table in the sample database.
The following query returns customer name, city, state, and country of all customers in the customers
table.
SELECT
customerName, city, state, country
FROM
customers;
Code language: SQL (Structured Query Language) (sql)
As you see, the state column has NULL values because some this information is not applicable to the country of some customers.
To substitute the NULL value in the result set, you can use the COALESCE
function as follows:
SELECT
customerName, city, COALESCE(state, 'N/A'), country
FROM
customers;
Code language: SQL (Structured Query Language) (sql)
In this example, if the value in the state column is NULL, the COALESCE
function will substitute it by the N/A
string. Otherwise, it returns the value of the state column.
Another typical example of using the COALESCE
function is to substitute value in one column by another when the first one is NULL.
Suppose you have an articles
table with the following structure:
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
excerpt TEXT,
body TEXT NOT NULL,
published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Code language: SQL (Structured Query Language) (sql)
Let’s insert some data into the articles
table.
INSERT INTO articles(title,excerpt,body)
VALUES('MySQL COALESCE Tutorial','This tutorial is about MySQL COALESCE function', 'all about COALESCE function'),
('MySQL 8.0 New Features',null, 'The following is a list of new features in MySQL 8.0');
Code language: SQL (Structured Query Language) (sql)
Imagine you have to display articles on an overview page where each article contains the title, expert, and publish date (and also the read more link the article page). The first task you need to do is to query this data from the articles
table:
SELECT
id, title, excerpt, published_at
FROM
articles;
Code language: SQL (Structured Query Language) (sql)
As you see the article with id 2 does not have the excerpt, which is not nice for displaying.
A typical solution is to get the first number of characters in the body
of the article for displaying as the excerpt. This is why the COALESCE
function comes into play.
SELECT
id, title, COALESCE(excerpt, LEFT(body, 150)), published_at
FROM
articles;
Code language: SQL (Structured Query Language) (sql)
In this example, if the value in the excerpt column is NULL, the COALESCE
function returns the first 150 characters of the content in the body
column.
MySQL COALESCE and CASE expression
Besides using the COALESCE
function, you can use the CASE
expression to achieve the same effect.
The following query uses the CASE
expression to achieve the same result as the example above:
SELECT
id,
title,
(CASE
WHEN excerpt IS NULL THEN LEFT(body, 150)
ELSE excerpt
END) AS excerpt,
published_at
FROM
articles;
Code language: SQL (Structured Query Language) (sql)
In this example, the CASE
expression is more lengthy than using the COALESCE
function.
MySQL COALESCE vs. IFNULL
The IFNULL
function takes two arguments and returns the first argument if it is not NULL, otherwise, it returns the second argument.
The IFNULL
function works great with two arguments whereas the COALESCE
function works with n
arguments. In case the number of arguments is two, both functions are the same.
In this tutorial, you have learned how to use the MySQL COALESCE function to substitute NULL values.