SQL COALESCE

Returns the first non-NULL value from a list — the standard way to supply a fallback for missing data, without writing a full CASE expression.

What & Why

COALESCE(value1, value2, ..., valueN) checks each argument in order and returns the first one that isn't NULL. If every argument is NULL, the whole expression returns NULL too. It's functionally equivalent to writing CASE WHEN value1 IS NOT NULL THEN value1 WHEN value2 IS NOT NULL THEN value2 ... ELSE NULL END — but far more compact for this specific, extremely common job.

See How It Works

BUSINESS QUESTION

Product wants every feedback row to show its NPS score or a readable fallback when no score was submitted.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

Watch COALESCE choose the first known valueExample 1 of 4

COALESCE(nps_score::text, 'No score')

id	nps_score	feedback_text
1	9	9
2	NULL	?
3	7	?
4	NULL	?

The score is known, so COALESCE stops at its first argument.

EXAMPLE QUERY

SELECT
  id AS feedback_id,
  nps_score,
  COALESCE(nps_score::text, 'No score') AS feedback_text
FROM product.feedback
ORDER BY feedback_id
LIMIT 20;

Now You Try

Practice this concept

Product wants every feedback row to show its NPS score or a readable fallback when no score was submitted.

Available schema

product

Prefix tables with product.table_name.

feedback_idnps_scorefeedback_text

product.feedback

Column	Type
id	integer
user_id	integer
feature_id	integer
rating	integer
nps_score	integer
comment	text
created_at	timestamp with time zone
moderation_bucket	text

query.sql

Intermediate business practice