Lesson 2 of 13 · 16 min
COALESCE and NULLIF
By the end of this lesson you can use COALESCE to supply fallbacks for NULL, use NULLIF to turn a value into NULL (for example to avoid division by zero), and decide when a default is honest and when it hides a problem.
Recall that NULL means no value recorded. It behaves like an unknown: 100 - NULL is NULL, 'Riyadh' || NULL is NULL, and NULL = NULL is not TRUE. That is logically right (you cannot subtract an unknown amount and know the answer) but it is often not what a report needs. Two functions give you control.
COALESCE: the first value that is not NULL
COALESCE(a, b, c, ...) looks at its arguments from left to right and returns the first one that is not NULL. If all of them are NULL, it returns NULL. It is perfect for fallbacks: show the email, or the phone if there is no email, or a placeholder if there is neither.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons