COALESCE usage and examples

Overview

COALESCE returns the first non-NULL expression from left to right. It is useful for display fallbacks, calculation defaults, and preventing NULL from propagating through an expression. It changes only the current query result, not stored data. Candidate expressions should use compatible types, and a fallback such as zero must reflect real business meaning rather than merely improving appearance.

Basic syntax

SELECT COALESCE(nickname, name, 'Unknown') FROM users;

Examples

COALESCE example

Try nickname, name, and the literal Unknown in order and observe which first non-NULL value each row returns.

Sample table

Table：users

users
name	nickname
Tom	NULL
Jack	J
Lucy	NULL

Run result

result
COALESCE(nickname, name, 'Unknown')
Tom
J
Lucy

Common issues

Does COALESCE change stored data?

No. It changes only the expression returned by the current query and does not update the table.

Must candidate values use the same type?

Use compatible types where possible, or the database may coerce values or reject the expression.

Is an empty string treated as NULL?

In MySQL an empty string and NULL are different and need separate business rules.

Keep reading

IFNULL NULLIF CASE WHEN IS NULL CAST

COALESCE