DEV Community

Cover image for What is COALESCE( ) in MySQL?
Ruthvik Raja M.V
Ruthvik Raja M.V

Posted on

1

What is COALESCE( ) in MySQL?

Syntax:COALESCE(expression_1, expression_2,..................); (In coalesce() more than one argument can be sent).

COALESCE() will always return a single value of the ones we have within parenthesis, and this value will be the first non-null value of this list, reading the values from left to right.

If COALESCE() has two arguments, it will work precisely like IFNULL().

IFNULL() and COALESCE() do not make any changes to the data set. They merely create an output where certain data values appear in place of NULL values.

Ex: Select dept_no, dept_name, coalesce(dept_manager, dept_name, "N/A") as dept_manager from departments;

Here in the 3rd column[Output] if there are no NULL values in the dept_manager then those values are printed else if there are null values in 3rd column then the value in dept_name column is printed, if the dept_name column has null values then N/A is printed.

COALESCE(expression_1): COALESCE() can also have only one argument.

Ex: Select dept_no, dept_name, COALESCE(“department manager name”) as fake_col from departments;
(This will print a 3rd column with “department manager name” as values in all the rows).

Here are some more Examples:

Question 1: Select coalesce(null, null, “Third”) as coalesce_test;
O/p: Third
Question 2: Select coalesce(“First”, null, “Third”) as coalesce_test;
O/p: First
Question 3: Select coalesce(“First”, “Second”, “Third”) as coalesce_test;
O/p: First
Question 4: Select coalesce(null, “Second”, null) as coalesce_test;
O/p: Second

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay