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