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 is 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 then while retrieving the name column we should replace the NULL value with a blank string using coalesce. In this article, we will learn about the syntax and usage of coalesce function using examples.

Syntax

Below is the syntax for MySQL COALESCE:

COALESCE(expression1,expressions,...);

Explanation: 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.

Examples to Implement MySQL COALESCE

Let us consider some examples:

Example #1

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 );

Output:

Example #2

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

Code:

SELECT COALESCE(NULL, NULL, NULL);

Output:

How does MySQL COALESCE work?

Let us see how does this function work:

Coalesce function to substitute NULL values

Step 1: 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;

Output:

Step 2: 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);

Output:

Step 3: Let us insert some more rows with 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:

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

Code:

SELECT firstName,rate,joining_date FROM educba_writers;

Output:

Step 5: Now, we will substitute the value of rate column when it is NULL with 0.00 and 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:

Explanation: 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

Step 1: There is one more place where we can use coalesce function besides substituting the null values with specified ones. 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. We will use the following query to create the table:

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:

Step 2: 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:

Step 3: Let us retrieve the records of the table:

Code:

SELECT * FROM  dictionary;

Output:

Step 4: Now, let us replace the NULL values of the meaning column with description column values for NULL values using the coalesce function. For that, we will use the following query statement:

Code:

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

Output:

Explanation: 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 meaning and description column. The function will go for searching the first non-null value. If the meaning column will contain 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 SELECT query.

Recommended Articles

This is a guide to MySQL COALESCE. Here we discuss an introduction to MySQL COALESCE, syntax, how does it work, examples. You can also go through our other related articles to learn more –

1. ROLLUP in MySQL
2. Condition in MySQL
3. SQL INSTR()
4. ANY in MySQL