How the COALESCE() function works in Mariadb?

The COALESCE() function is a conditional function that returns the first non-NULL value from a list of values. The COALESCE() function can be used to handle NULL values, or to provide default values when the desired values are missing.

Syntax

The syntax of the COALESCE() function is as follows:

COALESCE(value1, value2, ..., valueN)

Where:

• value1, value2, …, valueN are expressions that return values to be examined. The expressions can be of any data type, but they must be compatible with each other.

The return type of the function is the same as the data type of the first non-NULL value. If all the values are NULL, the function returns NULL.

Examples

Example 1: Returning the first non-NULL value

In this example, we use the COALESCE() function to return the first non-NULL value from a list of values. We use the SELECT statement to display the result.

SELECT COALESCE(NULL, 1, 2, 3) AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

Example 2: Returning a default value

In this example, we use the COALESCE() function to return a default value when the desired value is NULL. We use the products table as an example, which has the following structure and data:

DROP TABLE IF EXISTS products;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  description VARCHAR(100)
);

INSERT INTO products VALUES
(1, 'Laptop', 999.99, 'A high-performance laptop'),
(2, 'Mouse', 19.99, 'A wireless mouse'),
(3, 'Keyboard', 49.99, NULL),
(4, 'Monitor', 199.99, 'A 24-inch monitor'),
(5, 'Speaker', 29.99, NULL);

We use the SELECT statement to display the product name and the description. If the description is NULL, we use the COALESCE() function to return a default value of ‘No description available’.

SELECT name, COALESCE(description, 'No description available') AS description
FROM products;

The output is:

+----------+---------------------------+
| name     | description               |
+----------+---------------------------+
| Laptop   | A high-performance laptop |
| Mouse    | A wireless mouse          |
| Keyboard | No description available  |
| Monitor  | A 24-inch monitor         |
| Speaker  | No description available  |
+----------+---------------------------+

Example 3: Using the COALESCE() function in a WHERE clause

In this example, we use the COALESCE() function in a WHERE clause to filter the rows based on a condition that involves NULL values. We use the products table as an example, and we want to select only the products that have a price lower than the average price of all the products. However, some of the products have a NULL price, so we use the COALESCE() function to replace the NULL price with zero.

SELECT name, price
FROM products
WHERE COALESCE(price, 0) < (SELECT AVG(price) FROM products);

The output is:

+----------+--------+
| name     | price  |
+----------+--------+
| Mouse    |  19.99 |
| Keyboard |  49.99 |
| Monitor  | 199.99 |
| Speaker  |  29.99 |
+----------+--------+

Related Functions

Some other functions that are related to the COALESCE() function are:

• IFNULL(): Returns the first argument if it is not NULL, otherwise returns the second argument. The syntax is IFNULL(value1, value2).
• NULLIF(): Returns NULL if the two arguments are equal, otherwise returns the first argument. The syntax is NULLIF(value1, value2).
• ISNULL(): Returns 1 if the argument is NULL, otherwise returns 0. The syntax is ISNULL(value).
• CASE: Returns a value based on a set of conditions. The syntax is CASE WHEN condition1 THEN value1 [WHEN condition2 THEN value2 ...] [ELSE valueN] END.

For example, you can use the IFNULL() function to achieve the same result as the second example of the COALESCE() function, but with a different syntax:

SELECT name, IFNULL(description, 'No description available') AS description
FROM products;

The output is:

+----------+---------------------------+
| name     | description               |
+----------+---------------------------+
| Laptop   | A high-performance laptop |
| Mouse    | A wireless mouse          |
| Keyboard | No description available  |
| Monitor  | A 24-inch monitor         |
| Speaker  | No description available  |
+----------+---------------------------+

Conclusion

The COALESCE() function is a useful function to return the first non-NULL value from a list of values. The COALESCE() function takes one or more expressions as arguments, and returns the same data type as the first non-NULL value. If all the values are NULL, the function returns NULL. The COALESCE() function can be used to handle NULL values, or to provide default values when the desired values are missing. The COALESCE() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The COALESCE() function can be combined with other functions to perform various operations and analyses.