MySQL COALESCE() Function
The COALESCE() function in MySQL is designed to handle situations where you might have NULL values (missing or undefined values) in your data. It allows you to specify a list of values, and it returns the first value in that list that is not NULL.
COALESCE(): Definition and Usage
NULL values can sometimes cause problems in your queries or applications. COALESCE() provides a clean and efficient way to handle this by replacing NULLs with a value that you choose. The function evaluates the arguments one by one, from left to right, and returns the first argument that is not NULL. If all arguments are NULL, the function returns NULL.
Syntax
Syntax
COALESCE(val1, val2, ..., val_n)
Parameter Values
| Parameter | Description |
|---|---|
val1, val2, ..., val_n |
The values to check for NULL. At least one value is required. |
Examples
Returning the First Non-NULL Value
In this example, COALESCE() checks the list of values and returns the first one that isn't NULL.
Syntax
SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');
Output
W3Schools.com
Another Example with Numeric and String Values
This illustrates that COALESCE() handles different data types.
Syntax
SELECT COALESCE(NULL, 1, 2, 'W3Schools.com');
Output
1