SQL NULLs and COALESCE

You’ve written a query that looks perfect — WHERE age > 18 — yet some teenagers vanish from the results, and your report totals don’t add up. The culprit is almost always NULL, PostgreSQL’s way of saying "unknown," not "zero" and not "empty string." Until you internalize how NULL behaves in comparisons, arithmetic, and GROUP BY, your queries will quietly return wrong answers. In this lesson, you’ll understand NULL deeply and learn COALESCE — the function that turns missing values into something useful — so you can write queries that are correct, readable, and resilient.

The problem this lesson solves

NULL values are not a bug; they’re a feature of relational databases. But they break assumptions you bring from other languages:

• In Python, None is a value you can compare with ==. In SQL, NULL = NULL is not true — it’s still NULL.
• In spreadsheets, an empty cell is treated as zero in sums. In PostgreSQL, SUM() simply ignores NULLs, which can surprise you.
• A WHERE clause that excludes NULLs can silently drop rows you intended to keep.

These quirks lead to three classic production failures:

1. A LEFT JOIN produces NULLs for missing matches, and a subsequent WHERE filter removes those rows — turning the join into an inner join.
2. Arithmetic like price * quantity returns NULL if either operand is NULL, breaking invoices.
3. Sorting with ORDER BY puts NULLs first or last depending on your database — not what users expect.

This lesson gives you a mental model for NULL and a tool — COALESCE — to handle missing data intentionally. By the end, you’ll debug NULL-related bugs faster and write queries that behave predictably.

Core concept / mental model

Think of NULL as "unknown", not "empty" or "zero." If a customer’s age is NULL, we don’t know their age — it could be 20 or 80. So any comparison with NULL is indeterminate. That’s why age > 18 is neither true nor false for a NULL — it’s NULL, which the WHERE clause treats as false.

COALESCE is a function that returns the first non-NULL argument from a list:

COALESCE(value1, value2, ..., valueN)

Think of it as a safety net: "Use value1 if it’s not NULL, otherwise try value2, and so on, until you hit a fallback." It’s like Python’s or but explicit — it only triggers on NULL, not on falsy values like 0 or ''.

Here’s a mental model for NULL in different contexts:

Once you see NULL as "unknown," the behavior becomes logical.

How it works step by step

Let’s walk through how NULL and COALESCE work in a typical query.

Step 1 — Recognize NULL in data.

NULL appears when a column has no value: an optional field, a missing LEFT JOIN match, or an explicit INSERT with DEFAULT NULL.

Step 2 — Understand how NULL affects expressions.

• NULL = NULL returns NULL, not TRUE.
• NULL IN (1, 2, 3) returns NULL.
• NULL OR TRUE returns TRUE (because TRUE dominates), but NULL AND TRUE returns NULL.
• NOT NULL still returns NULL.

Step 3 — Use COALESCE to supply a fallback.

COALESCE(column, 'default') returns column if it’s not NULL, otherwise 'default'. You can chain multiple fallbacks: COALESCE(nickname, first_name, 'Anonymous').

Step 4 — Combine with other functions and clauses.

Use COALESCE inside SELECT, WHERE, ORDER BY, or GROUP BY to normalize NULLs before further processing.

Step 5 — Test with IS NULL / IS NOT NULL.

To actually check for NULL, use IS NULL or IS NOT NULL — never = NULL.

Step 6 — Handle NULL in joins and aggregation.

After a LEFT JOIN, filter with WHERE table2.id IS NOT NULL if you need only matched rows, or use COALESCE in the SELECT to show a placeholder.

Hands-on walkthrough

Let’s build a small table and experiment with NULL and COALESCE. Run these examples in psql or any PostgreSQL client.

Setup

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  nickname TEXT,
  age INT
);

INSERT INTO customers (name, nickname, age) VALUES
  ('Alice', 'Al', 30),
  ('Bob', NULL, NULL),
  ('Carol', 'Caz', 25);

SELECT * FROM customers;

Expected output:

 id | name  | nickname | age
----+-------+----------+-----
  1 | Alice | Al       |  30
  2 | Bob   |          |
  3 | Carol | Caz      |  25

Notice Bob’s nickname and age are NULL — the output shows blank, not 'NULL'.

Problem 1: WHERE excludes NULLs

SELECT name FROM customers WHERE age > 18;

Expected output:

 name
-------
 Alice
 Carol

Bob is missing because NULL > 18 is not true — it’s NULL, and WHERE only keeps rows where the condition is literally TRUE.

Problem 2: Arithmetic returns NULL

SELECT name, age, age * 2 AS double_age FROM customers;

Output:

 name  | age | double_age
-------+-----+------------
 Alice |  30 |         60
 Bob   |     |
 Carol |  25 |         50

Bob’s double_age is NULL, not 0. That’s the correct behavior for "unknown."

Problem 3: Fix with COALESCE

SELECT
  name,
  COALESCE(nickname, 'no nickname') AS display_name,
  COALESCE(age, 0) AS age_or_zero
FROM customers;

Output:

 name  | display_name | age_or_zero
-------+--------------+-------------
 Alice | Al           |          30
 Bob   | no nickname  |           0
 Carol | Caz          |          25

Now Bob shows a placeholder, and arithmetic won’t break.

Problem 4: COALESCE in WHERE and ORDER BY

SELECT name, COALESCE(age, 18) AS effective_age
FROM customers
WHERE COALESCE(age, 18) >= 18
ORDER BY effective_age DESC;

This treats missing age as 18 for filtering and sorting — a common pattern for seniority lists.

Check for NULLs

SELECT name FROM customers WHERE nickname IS NULL;

Returns Bob only — correct.

Compare options / when to choose what

COALESCE is not the only way to handle NULLs. Here’s a comparison:

Pro tip: Prefer COALESCE over CASE for simple fallbacks — it’s shorter and clearer. Use CASE only when you need multiple conditions.

For example, to replace a NULL with a 0 only when the column is NULL (not when it’s 0), COALESCE is perfect. To treat 0 as NULL (e.g., an unknown price), use NULLIF(price, 0).

Troubleshooting & edge cases

Here are the most common failures and fixes:

1. WHERE column = NULL never matches.

Use IS NULL instead. This is the #1 beginner mistake.

2. COALESCE evaluates all arguments — even unused ones.

If you call COALESCE(expensive_function(), 'fallback'), PostgreSQL may run expensive_function() even when the first argument is not NULL. Use a CASE to short-circuit if performance matters.

3. Data type mismatches.

COALESCE(text_col, 0) throws an error. All arguments must be compatible types. Cast explicitly: COALESCE(text_col, '0').

4. Sorting NULLs unexpectedly.

PostgreSQL puts NULLs last in ascending order, first in descending. To control, use ORDER BY age NULLS LAST or NULLS FIRST.

5. LEFT JOIN gone wrong.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.col = 1;

This drops rows with no match because b.col is NULL and fails the filter. Move the condition to the ON clause or check IS NOT NULL.

6. Unique constraints treat NULLs as distinct.

PostgreSQL allows multiple NULLs in a column with a unique index. For example, UNIQUE(email) permits many NULL emails. Use partial indexes for real uniqueness.

What you learned & what's next

You now understand that NULL means unknown, not empty — and that it propagates through comparisons, arithmetic, and joins. You’ve seen how WHERE excludes NULLs, how SUM ignores them, and how COALESCE gives you a clean way to provide fallback values. You practiced using COALESCE in SELECT, WHERE, and ORDER BY, and you know the alternatives like NULLIF and CASE.

Next lesson: In the next step of this PostgreSQL track, you’ll learn how to write a query that explicitly handles NULLs — using IS NULL and IS NOT NULL to filter and checking for NULLs in aggregate functions like COUNT. You’ll also see how COALESCE combines with GROUP BY to fill in gaps.

Action: Before moving on, ensure you can run the hands-on examples above and explain why NULL = NULL is not TRUE. Write a query that uses COALESCE to display "Unknown" for missing nicknames.