? COALESCE() Function in SQL

The COALESCE() function returns the first non-NULL value from a list of expressions.

? Syntax

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

• Evaluates the expressions from left to right.

• Returns the first expression that is NOT NULL.

• If all expressions are NULL, it returns NULL.

? Examples

1. Basic usage

SELECT COALESCE(NULL, NULL, 'Hello', 'World');-- Returns 'Hello'

2. Use with table columns

Suppose we have a table employees with columns phone_home, phone_mobile, and phone_work. To get the first available phone number:

SELECT employee_name,       COALESCE(phone_home, phone_mobile, phone_work, 'No Phone') AS contact_numberFROM employees;

3. Using COALESCE to avoid NULL in calculations

SELECT salary, bonus,       salary + COALESCE(bonus, 0) AS total_compensationFROM employees;

? Key Points

• Useful for handling NULL values gracefully.

• Supported by most SQL databases: MySQL, PostgreSQL, SQL Server, Oracle.

• Can take two or more arguments.

• Similar to IFNULL() or ISNULL() but supports multiple arguments.

Want me to compare COALESCE() with IFNULL() or ISNULL()?