COALESCEexpression

COALESCEexpression refers to the SQL standard function COALESCE, which returns the first non-NULL value from a list of expressions. If all provided expressions are NULL, the result is NULL.

Syntax and behavior

COALESCE(expression1, expression2, ..., expressionN) where N is at least 2. The function evaluates the arguments from left to right and returns the first argument that is not NULL. If every argument is NULL, the function returns NULL. The data type of the result is determined by the database’s type-resolution rules and typically corresponds to a common compatible type among the arguments, or the type of the first non-NULL argument in some dialects.

Equivalence and usage

COALESCE is commonly used to supply default values for missing data or to simplify conditional logic. It is standard SQL and widely supported across major database management systems. It is functionally equivalent to a CASE expression: COALESCE(a, b, c) is the same as CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END.

Examples

Selecting a contact method with a fallback: SELECT COALESCE(email, phone, 'no contact') FROM users. If email is NULL but phone is present, the result is the phone value; if all are NULL, the string 'no contact' is returned (in the example with a constant).

Relation to other constructs

COALESCE differs from NVL (Oracle) and ISNULL (some dialects) in that it is part of the ANSI SQL standard and can handle more than two arguments. It is useful for portable defaulting and simple null-handling in queries.

---