COALESCE in MARIADB

In MariaDB, the COALESCE function works similarly to other SQL databases like MySQL and PostgreSQL. It returns the first non-null expression among its arguments.

Here's the syntax for the COALESCE function in MariaDB:

COALESCE(expression1, expression2, ...)

- expression1, expression2, etc.: These are the expressions or values to be evaluated. They can be columns, literals, or any valid expressions.

Let's see an example to understand how COALESCE works in MariaDB:

Suppose we have a table named employees with columns name, salary, and bonus. Some employees have bonuses, while others do not. We want to select the salary and bonus for each employee, but if a bonus is NULL, we want to replace it with a default value of 0.

SELECT 

    name,

    salary,

    COALESCE(bonus, 0) AS bonus

FROM 

    employees;

In this query:

- We select the name and salary columns directly.

- We use COALESCE(bonus, 0) to select the bonus column. If bonus is NULL for any employee, it will be replaced with 0.

This ensures that even if an employee's bonus is NULL, the query will still return a value (0) for the bonus.

Here's another example demonstrating the use of COALESCE with literal values:

SELECT 

    name,

    COALESCE(salary, 1000) AS salary

FROM 

    employees;

In this query, if an employee's salary is NULL, it will be replaced with 1000.