COALESCE and NULLIF in SQL

Two small tools for missing data: pick the first non-NULL, or make a value NULL.

SELECT
    id,
    COALESCE(nickname, name, 'anonymous') AS display_name,
    ROUND(
        COALESCE(clicks, 0) * 100.0 / NULLIF(impressions, 0),
        2
    ) AS click_rate
FROM campaigns;

How it works

1. COALESCE returns its first argument that is not NULL.
2. NULLIF(a, b) yields NULL when the two are equal.
3. Together they guard a division against a zero denominator.

Keywords and builtins used here

• AS
• COALESCE
• FROM
• NULLIF
• SELECT

The run, in numbers

Lines

8

Characters to type

160

Tokens

41

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 26 seconds.

Type this snippet

Step 2 of 7 in Expressions, step 17 of 24 in Analytics & reporting.

← Previous Next →