MySQL COALESCE()

Introduction to MySQL COALESCE()

MySQL COALESCE function is used to handle the NULL values in the table columns. Whenever we create a table containing columns that have not been assigned a default value or auto-increment attribute, then the values that are inserted in that columns by default are NULL. While retrieving the data from the tables, it is not convenient and appropriate to display the NULL values to the user. Instead of NULL, we should replace the NULL value to some appropriate value depending on the purpose of that column and what type of value is being stored in them.

For example, when the name is stored, but the column contains default values in them when not specified while inserting, we should replace the NULL value with a blank string using coalesce while retrieving the name column.

Syntax of MySQL COALESCE()

Given below is the syntax of MySQL COALESCE():

COALESCE(expression1,expression2,...);

The coalesce function accepts the number of expressions which can be columns or values in the table or other expressions. It returns the first Non-Null value in the supplied comma-separated list as a parameter. But, we have to be careful while using it because if all the values and expressions evaluate to NULL, then the returned value is always NULL.

Let us consider some examples:

Firstly, we will make a list of values that will contain NULL as well as other valued elements.

Code:

SELECT COALESCE(NULL, 1, NULL, 0, true, false );

That gives the following output with the first non-Null value that is 1 as a result.

Now, we will query by using the coalesce in the list that contains only NULL values in it.

Output:

Code:

SELECT COALESCE(NULL, NULL, NULL);

Output:

COALESCE Function to Substitute NULL Values

Let us create one table named educba_writers using the following query statement.

Code:

CREATE TABLE `educba_writers` (
`id` int(11) NOT NULL,
`firstName` varchar(10) COLLATE latin1_danish_ci NOT NULL,
`rate` decimal(5,2) DEFAULT NULL,
`joining_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

Now we will insert some values in it.

Code:

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`) VALUES
(1, 'Payal', NULL, NULL),
(2, 'Vyankatesh', NULL, NULL),
(5, 'Om Prakash', NULL, NULL),
(6, 'Om Prakash', NULL, NULL);

Let us insert some more rows with a non-null rate and joining date value.

Code:

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`) VALUES
(1, 'Payal', 750, "2020-05-01"),
(2, 'Vyankatesh', 700, "2020-01-01"),
(5, 'Om Prakash', 600, "2020-02-01"),
(6, 'Om Prakash', 800, "2020-06-01");

Output:

Now, we will query on the educba_writers table to retrieve the values of columns firstName, rate, and joining date.

Code:

SELECT firstName,rate,joining_date FROM educba_writers;

Output:

Now, we will substitute the value of the rate column when it is NULL with 0.00 and the joining date column with “1990-01-01” by using the coalesce function by placing the column name in the first element of the list and the value that we have to substitute with when the column value is NULL in the second element in the coalesce list of parameters.

Our query for retrieving the records with substituted NULL values as per our requirement will be as follows.

Code:

SELECT firstName,COALESCE(rate,0.00),COALESCE(joining_date,"1990-01-01") FROM educba_writers;

Output:

We can see from the resultset that wherever there were any of the NULL values in the rate and joining_date column were replaced with 0.00 and “1990-01-01” values, respectively. This is where the coalesce function is used most of the time.

COALESCE Function to Substitute One Column with Other Value when First is NULL

There is one more place where we can use coalesce function besides substituting the null values with specified ones. For example, we can replace the NULL values of column records with some other column values using the coalesce function.

Let us create one new table named dictionary that will contain columns such as word, meaning, and description.

Code:

CREATE TABLE `dictionary` (
`word` varchar(100) DEFAULT NULL,
`meaning` varchar(5000) DEFAULT NULL,
`description` varchar(5000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Output:

Let us insert some values in it.

Code:

INSERT INTO `dictionary` (`word`, `meaning`, `description`) VALUES
('antonym', NULL,'a word that means the opposite of another word'),
('connotation', NULL,'an additional idea or emotion that a word suggests to you'),
('etymology', 'the study of the origins of words; the origins of a particular word','the study of the origins'),
('lexicography', 'the job or skill of writing dictionaries','writing dictionaries'),
('polysemy', NULL,'the fact that some words can have more than one meaning'),
('thesaurus', 'a reference tool which shows groups of words that have similar meanings','representation of groups of words that have similar meanings'),
('knack', 'an acquired or natural skill at doing something.','natural skill'),
('flair', 'stylishness and originality.','originality'),
('panache', 'a tuft or plume of feathers','feather collection');

Output:

Let us retrieve the records of the table.

Code:

SELECT * FROM  dictionary;

Output:

Now, let us replace the NULL values of the meaning column with description column values for NULL values using the coalesce function.

Code:

SELECT word, COALESCE(meaning,description) as meaning, description FROM dictionary;

Output:

We can see that the NULL values of the meaning column are replaced with the description column as we have used coalesce with a parameter list containing the meaning and description column. The function will go for searching the first non-null value. If the meaning column contains a value other than null, then that will be chosen while retrieving else if it is found null, then coalesce will search for the corresponding description column value and will display that value if it is not null and will display null if both meaning and description column are null.

IFNULL() Function Verses COALESCE Function:

• IFNULL() function works similarly as that of coalesce.
• The only difference between the IFNULL() and COALESCE() function is that IFNULL() function always accepts two values as its parameters while COALESCE() may accept any number of parameters in its parameter list.
• When COALESCE() function is used with two parameters, it works the same as IFNULL() function.

Conclusion

We can use the coalesce function to replace the null values in the column with any of our desired values that can be a constant string or number or even with any of the other column values while retrieving the column values in the SELECT query.

Recommended Articles

This is a guide to MySQL COALESCE(). Here we discuss the introduction, substitute NULL values and one column with other value when first is NULL. You may also have a look at the following articles to learn more –

1. MySQL sum()
2. MySQL Partition
3. MySQL VARCHAR
4. MySQL BLOB