🌱BeginnerNULL & Conditional Handling
COALESCE
Return the first non-NULL value from a list of expressions using COALESCE.
Overview
COALESCE(val1, val2, val3, ...) returns the first non-NULL value in the list. It is the standard SQL way to provide a default value when a column might be NULL. If all values are NULL, COALESCE returns NULL.
COALESCE is evaluated lazily it stops at the first non-NULL value and does not evaluate the rest. This makes it safe to use with expressions that could have side effects or be expensive. It is equivalent to a CASE WHEN expression: COALESCE(a, b) = CASE WHEN a IS NOT NULL THEN a ELSE b END.
Key Points
- •COALESCE(col, default) if col is NULL, return default
- •Can take any number of arguments: COALESCE(a, b, c, d)
- •Returns NULL only if all arguments are NULL
- •Evaluated lazily (stops at first non-NULL)
- •NULLIF(a, b) is the inverse: returns NULL when a = b
Examples
Providing default values
-- Show 0 if discount is NULL
SELECT name, COALESCE(discount, 0) AS discount FROM products;
-- Show 'Anonymous' if display_name is NULL
SELECT COALESCE(display_name, username, 'Anonymous') AS shown_name FROM users;COALESCE in UPDATE
-- Update: keep existing value if new value is NULL (partial update pattern)
UPDATE users
SET
email = COALESCE($1, email),
phone = COALESCE($2, phone)
WHERE id = $3;This pattern is useful for PATCH endpoints that only update provided fields.
Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4