The MySQL COALESCE() function is used for returning the first non-null value in a list of expressions. If all the values in the list evaluate to NULL, then the COALESCE() function returns NULL.
The COALESCE() function accepts one parameter which is the list which can contain various values. The value returned by the MySQL COALESCE() function is the first non-null value in a list of expressions or NULL if all the values in a list are NULL.
Syntax:
COALESCE(value_1, value_2, ...., value_n)
Parameters Used:
- value_1 – It is used to specify the first value in the list.
Return Value:
The MySQL COALESCE() function returns the first non-null value in a list of expressions or NULL if all the values in a list are NULL.
Supported Versions of MySQL:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Example-1: Implementing COALESCE() function on a list.
SELECT COALESCE(NULL, 'A', 'B', NULL);
Output:
A
Example-2: Implementing COALESCE() function on a list.
SELECT COALESCE('A', NULL, 'B', NULL); Output:
A
Example-3: Implementing COALESCE() function on a list.
SELECT COALESCE(NULL, 1, 2, 3, NULL, 'B', NULL);
Output:
1
Example-4: Implementing COALESCE() function on a list.
SELECT COALESCE(NULL, NULL, 'geeksforgeeks', NULL);
Output:
geeksforgeeks