NULL Handling (COALESCE, IS NULL)

NULL is SQL's way of saying "we don't know" — a missing phone number, an unset date, an unanswered field. It follows surprising rules that quietly break naive queries, so handling it correctly is one of the most valuable SQL skills you can build.

Learn NULL Handling (COALESCE, IS NULL) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a…

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn why = NULL never works, how to test for missing values with IS NULL , supply defaults with COALESCE , dodge divide-by-zero with NULLIF , and how aggregates quietly skip NULLs.

Note the empty cells — those are NULLs, the "we don't have this" markers we'll learn to handle.

(NULL) marks a missing value — there's nothing actually stored, not an empty string.

NULL represents the absence of a value. Because it means "unknown", any comparison with it returns UNKNOWN — not true, not false. That's why phone = NULL matches nothing. You must use the dedicated IS NULL / IS NOT NULL operators.

NULL is a sealed envelope with no address written on it. You can't ask "is this address equal to that one?" — there's nothing to compare. You can only ask "is the address missing?" — that's IS NULL .

COALESCE takes any number of arguments and returns the first one that isn't NULL. It's the go-to tool for "use this, or fall back to that, or finally show a placeholder" — turning ragged, gap-filled data into clean output.

Add the operator that finds rows where a column has no value.

NULL is contagious : any arithmetic involving NULL produces NULL, so base_pay + bonus becomes NULL the moment a bonus is missing. Wrap nullable terms in COALESCE(..., 0) to neutralize them. The mirror-image function NULLIF deliberately produces a NULL — most famously to make division by zero safe.

Aggregate functions silently ignore NULLs — except COUNT(*) , which counts every row. This means AVG(bonus) divides by the number of non-NULL bonuses, which can differ from the row count and surprise you.

Unscramble these lines into a query that lists each staff member and a total pay where a missing bonus counts as 0, skipping rows with no base pay.

Why: the SELECT list defines total_pay , where COALESCE(bonus, 0) stops a missing bonus from poisoning the sum into NULL. Then FROM names the table and WHERE base_pay IS NOT NULL excludes rows with no base pay — using IS NOT NULL rather than != NULL , which would never match.

Predict each result, then reveal. Use the contacts table.

0 — always. Comparing to NULL with = yields UNKNOWN, so no row qualifies. You needed email IS NULL (which would return Leo and Sam).

'N/A' — Priya's phone and mobile are both NULL, so COALESCE falls through to the literal default.

SUM = 30, COUNT = 2 — aggregates ignore the NULL entirely. COUNT(*) on the same three rows would be 3.

Build a display_email with no NULLs using COALESCE and a final placeholder. Expected shape is in the comments.

Practice quiz

What does NULL represent in SQL?

  • The number zero
  • An empty string
  • The absence of a value — 'unknown'
  • A boolean false

Answer: The absence of a value — 'unknown'. NULL means the absence of a value ('we don't know'). It is not zero, not an empty string, and not equal to anything.

Why does WHERE phone = NULL return zero rows?

  • Because NULL is the same as an empty string
  • Because comparing anything to NULL yields UNKNOWN, never TRUE
  • Because = NULL is a syntax error
  • Because NULL equals every value, so nothing is selected

Answer: Because comparing anything to NULL yields UNKNOWN, never TRUE. Any comparison with NULL returns UNKNOWN (not TRUE), so no row qualifies. You must use IS NULL / IS NOT NULL.

Which operator correctly tests for a missing value?

  • = NULL
  • != NULL
  • IS NULL
  • == NULL

Answer: IS NULL. IS NULL (and IS NOT NULL) are the dedicated operators for testing the absence or presence of a value.

What does COALESCE(phone, mobile, 'No contact') return?

  • The last non-NULL argument
  • The first non-NULL argument, left to right
  • Always the literal 'No contact'
  • NULL if any argument is NULL

Answer: The first non-NULL argument, left to right. COALESCE returns the first non-NULL value from its arguments, falling through left to right.

What is the result of base_pay + bonus when bonus is NULL?

  • base_pay unchanged
  • Zero
  • NULL — NULL is contagious in arithmetic
  • An error

Answer: NULL — NULL is contagious in arithmetic. NULL is contagious: any arithmetic involving NULL produces NULL. Wrap nullable terms in COALESCE(..., 0) to neutralize them.

What does NULLIF(cost, 0) do, and why is it useful?

  • Returns 0 when cost is NULL; rounds the value
  • Returns NULL when cost equals 0 — making division by zero safe
  • Returns the larger of cost and 0
  • Converts cost to a string

Answer: Returns NULL when cost equals 0 — making division by zero safe. NULLIF(a, b) returns NULL when a equals b. NULLIF(cost, 0) turns a zero divisor into NULL so the division yields NULL instead of crashing.

How do most aggregate functions (SUM, AVG, COUNT(col)) treat NULLs?

  • They count NULLs as zero
  • They raise an error
  • They ignore NULLs entirely
  • They convert NULLs to empty strings

Answer: They ignore NULLs entirely. Aggregates like SUM, AVG, MIN, MAX, and COUNT(col) ignore NULLs. Only COUNT(*) counts every row regardless.

For a column with values 10, NULL, 20, what are COUNT(*) and COUNT(col)?

  • Both 3
  • Both 2
  • COUNT(*) = 3 and COUNT(col) = 2
  • COUNT(*) = 2 and COUNT(col) = 3

Answer: COUNT(*) = 3 and COUNT(col) = 2. COUNT(*) counts every row (3), while COUNT(col) skips the NULL and counts only the non-NULL values (2).

Why prefer COALESCE over IFNULL, ISNULL, or NVL?

  • COALESCE is faster
  • COALESCE is ANSI standard and works everywhere; the others are vendor-specific 2-argument variants
  • COALESCE can take only one argument
  • The others cannot handle NULLs

Answer: COALESCE is ANSI standard and works everywhere; the others are vendor-specific 2-argument variants. COALESCE is ANSI standard and portable. IFNULL (MySQL/SQLite), ISNULL (SQL Server), and NVL (Oracle) are 2-argument vendor variants.

How is NULL different from an empty string ('')?

  • They are identical in SQL
  • '' is a real value; NULL is the absence of one — they behave differently in comparisons and aggregates
  • Empty string is always converted to NULL
  • NULL is a special kind of empty string

Answer: '' is a real value; NULL is the absence of one — they behave differently in comparisons and aggregates. An empty string is a real stored value, whereas NULL is the absence of a value, so they behave differently in comparisons and aggregates.