case

COALESCE: Handling NULLs in Output

Use COALESCE to replace NULL manager names with a friendly placeholder.

Overview

COALESCE(expr, …) returns the first non-null argument — a concise way to substitute defaults for NULL values in your output. It's cleaner than a CASE WHEN expr IS NULL THEN … END and works in SELECT, WHERE, and ORDER BY. Here we list each employee next to their manager, showing "—" when there's no manager.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

LEFT JOIN employees m ON e.manager_id = m.id — keeps employees even when manager_id is NULL.
COALESCE(m.first_name || ' ' || m.last_name, '—') — when the manager is NULL, the concatenation is NULL, so COALESCE falls back to '—'.
COALESCE accepts two or more arguments and returns the first non-null one.

COALESCE vs CASE

COALESCE(a, b) is equivalent to CASE WHEN a IS NOT NULL THEN a ELSE b END but is far more readable, especially with several fallbacks.

Variations

Show total payroll with zero for missing values:

Employees & Departments

Loading database engine...

Common Mistakes

Wrong argument order. COALESCE(fallback, real) returns the fallback first if it's non-null — list the preferred value first.
Mismatched types. All arguments must share a type; COALESCE(name, 0) errors. Use COALESCE(name, '0') or cast.
Confusing with NULLIF. NULLIF(a, b) does the opposite — turns a matching value into NULL.