EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL COALESCE()
 

MySQL COALESCE()

Updated June 2, 2023

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL coalesce 1

Code:

SELECT COALESCE(NULL, NULL, NULL);

Output:

MySQL coalesce 2

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:

MySQL coalesce 3

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:

MySQL coalesce 4

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:

MySQL coalesce 5

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:

one new table named dictionary

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:

insert some values

Let us retrieve the records of the table.

Code:

SELECT * FROM dictionary;

Output:

retrieve the records

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:

replace the NULL values

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW