What is COALESCE function?

The COALESCE function in SQL is a powerful and widely used function that evaluates its arguments in order and returns the first expression that is not NULL. It is a standard SQL function available across most relational database management systems.

What is COALESCE?

COALESCE is a scalar function that takes multiple arguments. It iterates through the arguments from left to right and returns the value of the first argument it encounters that is not NULL. If all arguments are NULL, the function returns NULL.

Syntax

COALESCE(expression1, expression2, ..., expressionN)

Where expression1, expression2, ..., expressionN are the expressions to be evaluated. These expressions can be columns, literals, or other functions, but they must all be of a compatible data type.

How it Works (Step-by-Step)

• The function starts by evaluating expression1.
• If expression1 is not NULL, its value is returned immediately.
• If expression1 is NULL, the function proceeds to evaluate expression2.
• If expression2 is not NULL, its value is returned.
• This process continues for all subsequent expressions.
• If all expressions (from expression1 to expressionN) are NULL, the function returns NULL.

Practical Examples

Consider a scenario where you want to display a customer's preferred name. They might have a 'Nickname', or if not, a 'FirstName', and if all else fails, a 'DefaultUser' string.

SELECT
    CustomerID,
    COALESCE(Nickname, FirstName, 'DefaultUser') AS DisplayName
FROM
    Customers;

In this example, for each customer:

• If Nickname is not NULL, Nickname is returned as DisplayName.
• If Nickname is NULL but FirstName is not NULL, FirstName is returned.
• If both Nickname and FirstName are NULL, the literal string 'DefaultUser' is returned.

Another example demonstrates retrieving a valid phone number from several possible columns:

SELECT
    ContactID,
    COALESCE(MobilePhone, WorkPhone, HomePhone, 'No Phone Available') AS PrimaryPhoneNumber
FROM
    Contacts;

Common Use Cases

• Providing default values for NULL columns in query results.
• Handling missing data gracefully in reports and applications.
• Merging data from multiple columns, prioritizing non-NULL values.
• Ensuring expressions always return a non-NULL value when a NULL would cause issues (e.g., in calculations or concatenations).

COALESCE vs. ISNULL / NVL

While COALESCE serves a similar purpose to functions like ISNULL (SQL Server) or NVL (Oracle, PostgreSQL), there are key differences:

• COALESCE is part of the SQL standard and is highly portable across different database systems.
• ISNULL and NVL are vendor-specific functions. ISNULL typically takes only two arguments, and NVL usually takes two or three depending on the database.
• COALESCE can take two or more arguments, allowing for a more complex chain of fallback values.