{"id":5543,"date":"2017-02-05T04:30:52","date_gmt":"2017-02-05T11:30:52","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5543"},"modified":"2023-10-10T01:37:43","modified_gmt":"2023-10-10T08:37:43","slug":"mysql-coalesce","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-comparison-functions\/mysql-coalesce\/","title":{"rendered":"MySQL COALESCE Function"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to use the MySQL COALESCE<\/code> function to substitute NULL values.<\/p>\n\n\n\n

Introduction to MySQL COALESCE function<\/h2>\n\n\n\n

The COALESCE <\/code>function takes a number of arguments and returns the first non-NULL argument. In case all arguments are NULL, the COALESCE<\/code> function returns NULL.<\/p>\n\n\n\n

The following illustrates the COALESCE<\/code> function syntax:<\/p>\n\n\n

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