MySQL COALESCE() Function

The COALESCE() function in MySQL returns the first non-NULL value from a list of expressions. This function is often used to provide a default value when NULL values are encountered in columns or expressions.

Syntax

COALESCE(val1, val2, ..., val_n)

Parameters

Definition and Usage

• The COALESCE() function evaluates the arguments in order and returns the first non-NULL value.
• If all values are NULL, it will return NULL.

Technical Details

• Works in: From MySQL 4.0.
• Return Type: Returns the first non-NULL value from the list of arguments.

Example Usage

Example 1: Return the First Non-NULL Value

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

This query returns 'W3Schools.com', as it is the first non-NULL value in the list.

Example 2: Return the First Non-NULL Value from Numeric and String Values

SELECT COALESCE(NULL, 1, 2, 'W3Schools.com');

This query returns 1, as it is the first non-NULL value in the list.

Example 3: Handle NULLs in a Column

You can use COALESCE() to replace NULL values in a column with a default value:

SELECT CustomerName, COALESCE(Phone, 'No phone number available') AS PhoneNumber
FROM Customers;

This query will return a default message ('No phone number available') when the Phone column contains NULL.

Use Cases

• Default Values: Useful for replacing NULL with default values in queries.
• Handling Missing Data: When retrieving data from multiple columns, COALESCE() helps return the first available value.