The MySQL COALESCE function is a useful comparison function for handling NULL values in queries. It allows you to replace NULL values with a specified alternative value. The primary purpose of COALESCE is to return the first non-NULL expression among its arguments.

Syntax

Here’s the syntax for the COALESCE function:

COALESCE(expr1, expr2, ...)

expr1, expr2, …: These are the expressions or values to be evaluated. The function returns the first non-NULL expression from the list.

Example

Now, let’s look at an example to illustrate the usage of the COALESCE function:

Consider a scenario where you have a table named employees with columns employee_id, employee_name, and salary. Some salary values are NULL, and you want to display a default value in case the salary is NULL.

SELECT employee_id, employee_name, 
COALESCE(salary, 0) AS adjusted_salary
FROM employees;

In this example, if the salary column is NULL, the COALESCE function will replace it with the value 0. The result set will contain the employee_id, employee_name, and the adjusted salary. The adjusted salary will be 0 for records where the original salary was NULL.

You can also use COALESCE with multiple columns:

SELECT employee_id, employee_name, 
COALESCE(salary, bonus, 0) AS total_compensation
FROM employees;

In this case, the COALESCE function will return the first non-NULL value among salary, bonus, and 0 as the total_compensation.

Comparison with IFNULL

Another similar function in MySQL is IFNULL, which takes only two arguments: the first expression and the default value to return if the first expression is null. COALESCE is more flexible as it can handle multiple expressions and evaluate them in a specific order.

Benefits of Using COALESCE

Handles multiple null values in a single query.
Provides more control over the order of evaluation.
Avoids unnecessary IF statements.
Makes code more readable and maintainable.

In summary, the COALESCE function in MySQL is a handy tool to deal with NULL values in a concise and readable manner, providing a way to handle and replace them with default or alternative values during query results.