DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

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

Posted on

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

Top comments (0)

5 Websites To Learn Frontend Web Development Faster πŸ’¨

>> Check out this classic DEV post <<