COALESCE(value,...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. At least one parameter must be passed.
See also NULL Values in MariaDB.
SELECT COALESCE(NULL,1); +------------------+ | COALESCE(NULL,1) | +------------------+ | 1 | +------------------+
SELECT COALESCE(NULL,NULL,NULL); +--------------------------+ | COALESCE(NULL,NULL,NULL) | +--------------------------+ | NULL | +--------------------------+
When two arguments are given, COALESCE() is the same as IFNULL():
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 | +------+------+
The reason for the differing results above is that when 0x31 is inserted directly to the column, it's treated as a number (see Hexadecimal Literals), while when 0x31 is passed to COALESCE(), it's treated as a string, because:
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/coalesce/