NULL Functions: ISNULL, COALESCE, NULLIF

Reports should say "Unsold", not NULL. Display names should fall back to the first name when there is no nickname. And a bowler who faced 0 balls must not crash your strike-rate query. Three NULL functions handle all of this.

Definition

ISNULL(value, replacement_value) returns value if it is not NULL; otherwise it returns replacement_value. It accepts exactly two arguments (SQL Server only).

COALESCE(value1, value2, value3, ...) returns the first argument that is not NULL, checking from left to right. It accepts two or more arguments and is ANSI-standard. If every argument is NULL, it returns NULL.

NULLIF(value1, value2) returns NULL if the two values are equal; otherwise it returns value1. It creates a NULL on purpose.

Syntax

ISNULL(value, replacement_value)
COALESCE(value1, value2 [, value3 ...])
NULLIF(value1, value2)

Example — the functions on literals

SELECT
    ISNULL(NULL, 'Unsold')          AS isnull_null,
    ISNULL('RCB', 'Unsold')         AS isnull_value,
    ISNULL(NULL, 0)                 AS isnull_zero,
    COALESCE(NULL, NULL, 'Thala')   AS coalesce_3,
    COALESCE(NULL, 'Dhoni', 'MS')   AS coalesce_2,
    COALESCE('King', 'Virat')       AS coalesce_1,
    NULLIF(10, 10)                  AS nullif_same,
    NULLIF(10, 5)                   AS nullif_diff;

Example — ISNULL in a query

SELECT
    name,
    ISNULL(team, 'Unsold') AS team,
    runs
FROM player_profiles;

How it works: step 1 — read the team; step 2 — if it is NULL use 'Unsold', otherwise keep it. Warner has no team, so the report shows 'Unsold'.

Example — COALESCE for a display name

SELECT
    first_name,
    nickname,
    COALESCE(nickname, first_name) AS display_name
FROM player_profiles;

Show the nickname when it exists, otherwise fall back to the first name. With more arguments you can chain fallbacks: COALESCE(nickname, first_name, name, 'Unknown').

Example — NULLIF to avoid divide-by-zero

A scorecard where Bumrah faced 0 balls:

SELECT
    name,
    runs,
    balls,
    ROUND(runs * 100.0 / NULLIF(balls, 0), 1) AS sr
FROM match_scores;

How it works: step 1 — NULLIF(balls, 0) turns 0 into NULL; step 2 — anything divided by NULL is NULL. Without NULLIF, SQL Server stops the whole query with "Divide by zero error encountered"; with it you get a harmless NULL. (SQL Server displays the numbers with trailing zeros, e.g. 150.000000. The browser playground returns NULL even without NULLIF — but write NULLIF anyway, because SQL Server won't.)

Example — NULLs in calculations

Any arithmetic with NULL gives NULL, so one missing value can wipe out a total. MS Dhoni has a NULL innings (did not bat):

SELECT
    player,
    match_date,
    runs,
    runs + 10            AS plus_bonus,
    ISNULL(runs, 0) + 10 AS plus_bonus_safe
FROM innings
WHERE player = 'MS Dhoni';

Warning: Only replace NULL with 0 when "missing" truly means zero. "Did not bat" is not the same as "scored 0" — replacing it changes averages.

ISNULL vs COALESCE

Example of the type trap: if team is VARCHAR(3), ISNULL(team, 'Unsold') can be cut to 'Uns' in SQL Server because the result takes the first argument's type. COALESCE doesn't have this problem. Prefer COALESCE for portable code.

Common mistakes

• ❌ ISNULL(a, b, c) → error; ISNULL takes exactly 2. ✅ COALESCE(a, b, c).
• ❌ runs / balls when balls can be 0 → divide-by-zero error. ✅ runs / NULLIF(balls, 0).
• ❌ Mixing types: ISNULL(runs, 'N/A') — runs is INT, so SQL Server tries to convert 'N/A' to INT and fails. ✅ COALESCE(CAST(runs AS VARCHAR(10)), 'N/A').
• ❌ Confusing ISNULL() (function) with IS NULL (condition).

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: How do you prevent a divide-by-zero error in SQL? Wrap the divisor in NULLIF(divisor, 0). A zero becomes NULL, the division returns NULL instead of failing, and you can then COALESCE the result to 0 or a label if needed.