conversion

COALESCE()

Returns the first non-null value from a list of expressions.

Description

COALESCE evaluates a list of expressions in order and returns the first one that is not NULL. It's the standard way to provide a fallback or default value when a column might be missing data, without writing a CASE expression by hand.

Syntax

COALESCE(value1, value2, ...)

Parameters

Name	Description	Optional
value1, value2, ...	Any number of expressions, checked left to right. The result is the first non-NULL one, or NULL if every expression is NULL.	No (at least one argument is required)

Return Type

COALESCE returns a value with the same type as its arguments (they should all be compatible types), whether that's a number, text, or date.

Examples

Employees & Departments

Loading database engine...

Employees & Departments

Loading database engine...

COALESCE is great for friendly labels

Instead of showing a raw NULL to end users, wrap the column in COALESCE to substitute something readable, like 'No manager' or 'Unassigned', right inside the query.

Common Mistakes

Assuming COALESCE only takes two arguments. It accepts any number of expressions and returns the first non-NULL one, checking as many as needed.
Mixing incompatible types across arguments. COALESCE(manager_id, 'None') mixes a number with text, which can error or produce unexpected type coercion; casting to a common type first (like VARCHAR) avoids this.
Using COALESCE where 0 and NULL should be treated differently. If a numeric column can legitimately be 0, COALESCE(column, -1) won't distinguish "value is zero" from "value is missing" unless you pick a fallback that can't occur naturally.