COALESCE

Definition

COALESCE(val1, val2, ..., valN) returns the FIRST non-NULL value among its arguments, evaluated left to right — the standard way to supply a fallback/default when a value might be NULL.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name, COALESCE(stock, 0) AS stock FROM products;

For Notebook (whose stock is NULL), this returns 0 instead of NULL — COALESCE checked its first argument (stock), found it NULL, and fell through to the second argument (the literal 0). For every other product, stock itself is already non-NULL, so it's returned unchanged.

Edge Cases and Pitfalls

• COALESCE can take MORE than two arguments, checking each in order: COALESCE(preferred_phone, backup_phone, office_phone, 'No phone on file') tries each option in turn.
• COALESCE is standard SQL, supported essentially everywhere — unlike dialect-specific two-argument shortcuts like MySQL's IFNULL() or Oracle's NVL() (Chapter 14), which only accept exactly two arguments. COALESCE is generally the more portable choice.
• All arguments to COALESCE should be of compatible types — mixing incompatible types (e.g. a number and an unconvertable string) can cause an error or an unexpected implicit conversion, depending on the dialect.

Key Takeaways

• COALESCE(a, b, c, ...) returns the first non-NULL value among its arguments, checked left to right.
• It's the standard, portable way to supply a fallback for a possibly-NULL value — preferred over dialect-specific two-argument alternatives.
• It accepts any number of arguments, not just two.