Bobcares

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till December 6th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till December 6th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

Coalesce In MariaDB | All About

by | Nov 28, 2022

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:

coalesce in mariadb

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.