Let’s look into the details of coalesce function in MariaDB. At Bobcares, with our Server Management Services, we can handle your server issues.

Coalesce function in MariaDB

We can use the MariaDB Coalesce function to get the first NON-NULL expression in a list. When displaying data, the method is helpful for replacing null values with default values.

The syntax of the function is as follows:

COALESCE( expression1, expression2, ... expression_n )

Coalesce function in MariaDB – Key points

1. The parameters of the function, expression1, expression2, … expression_n are the expressions to test for NON-NULL values. The MariaDB COALESCE function will return NULL if every expression evaluates to NULL.

2. This method returns the current value of the first expression that initially does not evaluate to NULL. For e.g.,

SELECT COALESCE( null, 5 * 2 ); will return 10

3. If we use COALESCE() without any arguments, then it will result in an error. For e.g.,

SELECT COALESCE(); will return the below message:

4. If there are no non-NULL values, the COALESCE function returns NULL. There must be at least one passed argument. For e.g.,

SELECT COALESCE( null, null ); will return the result as NULL.

5. The function results in the same output as IFNULL when used with two values. For e.g.,

SET @a = null, @b = 50;
SELECT 
COALESCE(@a, @b), 
IFNULL(@a, @b);

Output

+------------------+----------------+
| COALESCE(@a, @b) | IFNULL(@a, @b) |
+------------------+----------------+
| 50 | 50 |
+------------------+----------------+

Examples

SELECT COALESCE(NULL,1);
+------------------+
| COALESCE(NULL,1) |
+------------------+
|                1 |
+------------------+

SELECT COALESCE(NULL,NULL,NULL);
+--------------------------+
| COALESCE(NULL,NULL,NULL) |
+--------------------------+
|                     NULL |
+--------------------------+

COALESCE() and IFNULL() are equivalent when two parameters are supplied:

SET @a=NULL, @b=1;

SELECT COALESCE(@a, @b), IFNULL(@a, @b);
+------------------+----------------+
| COALESCE(@a, @b) | IFNULL(@a, @b) |
+------------------+----------------+
|                1 |              1 |
+------------------+----------------+

Hex-type confusion:

CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (0x31, 0x61),(COALESCE(0x31), COALESCE(0x61));

SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|   49 | a    |
|    1 | a    |
+------+------+

When 0x31 is entered directly into the column, it is handled as a number, but when 0x31 is supplied to COALESCE(), it is processed as a string, which explains why the results above are different.

When the aggregate method returns NULL after finding no rows, replace NULL with 0 as follows:

SELECT SUM(score) FROM student;
+------------+
| SUM(score) |
+------------+
|       NULL |
+------------+

SELECT COALESCE(SUM(score),0) FROM student;
+------------------------+
| COALESCE(SUM(score),0) |
+------------------------+
|                      0 |
+------------------------+

[Need help with another issue? We are just a click away.]

Conclusion

The article provides a detailed description of the Coalesce function in MariaDB along with examples from our Support team.