Updated June 2, 2023
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 without a default value or auto-increment attribute, the values inserted in those columns by default are NULL. While retrieving the data from the tables, displaying the NULL values to the user is not convenient and appropriate. Instead of using NULL, we should replace the NULL value with a proper value that depends on the column’s purpose and the type of value being stored in it.
For example, when storing the name but the column contains default values if not specified during insertion, we should replace the NULL value with a blank string when retrieving the name column using coalesce.
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 must exercise caution when using it because if all the values and expressions are evaluated to NULL, the returned value will always be NULL.
Examples of MySQL COALESCE()
Let us consider some examples:
Firstly, we will make a list of values that will contain NULL and other valued elements.
Code:
SELECT COALESCE(NULL, 1, NULL, 0, true, false );That gives the following output with the first non-Null value, 1, as a result.
Now, we will query by using the coalesce in the list containing only NULL values.
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 into 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 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 containing 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 observe that we replace the NULL values in the meaning column with the description column by using 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, that will be chosen while retrieving else; if it is found null, it will search for the corresponding description column value and display that value if it is not null and will display null if both the meaning and description columns are null.
IFNULL() vs COALESCE()
- IFNULL() function works similarly to that of coalesce.
- The only difference between the IFNULL() and COALESCE() functions is that IFNULL() function always accepts two values as its parameters. In contrast, 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, a constant string or number, or even any other column values while retrieving the column values in the SELECT query.
Recommended Articles
We hope that this EDUCBA information on “MySQL COALESCE()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
