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)