COALESCE in MYSQL

In MySQL, the COALESCE() function is used to return the first non-null expression in a list. It takes multiple arguments and returns the first non-null value from them. If all arguments are null, it returns null. 

Here's the syntax:

COALESCE(value1, value2, ..., valuen)

- value1, value2, ..., valuen are the values or expressions to be evaluated. 

Let's see an example:

Suppose we have a table employees with columns id, name, salary, and bonus. Some employees might not have a bonus specified, and we want to display a default value of 0 for their bonus.

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    salary DECIMAL(10, 2),

    bonus DECIMAL(10, 2)

);

INSERT INTO employees (id, name, salary, bonus) VALUES

(1, 'John', 50000.00, 2000.00),

(2, 'Alice', 60000.00, NULL),

(3, 'Bob', 55000.00, NULL);

Now, let's use COALESCE() to handle null values in the bonus column:

SELECT 

    id,

    name,

    salary,

    COALESCE(bonus, 0) AS bonus

FROM 

    employees;

This query will return:

| id | name  | salary   | bonus  |

|----|-------|----------|--------|

| 1  | John  | 50000.00 | 2000.00|

| 2  | Alice | 60000.00 | 0.00   |

| 3  | Bob   | 55000.00 | 0.00   |

As you can see, for employees without a bonus specified (like Alice and Bob), COALESCE() returns 0 as the default value. For John, who has a bonus specified, it returns the actual bonus value.