NULL handling: what 'blank' really means in SQL

NULL is SQL's way of saying "there's no value here / we don't know it." It is not zero, not an empty piece of text, and not false. Treating it like a normal value is the source of a huge share of beginner bugs.

🎯 Explain Like I'm Hired If a customer's phone number is NULL, that doesn't mean "no phone." It means "we don't know their phone." And when you compare something unknown to anything else, SQL's answer isn't yes or no, it's "unknown," and rows that come back "unknown" get silently dropped from your results. Example: WHERE status != 'cancelled' looks like it keeps everything that isn't cancelled, but it silently drops rows where status is blank, because "is blank ≠ cancelled?" is unknown, not true. The fix: WHERE status != 'cancelled' OR status IS NULL.

Because a blank is "unknown," you can't test it with = or !=. You have to use the special IS NULL / IS NOT NULL:

SELECT 1 WHERE NULL = NULL;    -- returns nothing (the answer is "unknown", not true)
SELECT 1 WHERE NULL IS NULL;   -- returns 1  (the correct way to check for blank)

Totals quietly skip blanks

SUM, AVG, MIN, MAX all ignore blank values rather than treating them as zero. The average of 10, (blank), 20 is 15, not 10. That's usually what you want, but it means a column that's mostly blank can still show a healthy-looking average from the few filled-in rows.

COALESCE fills in a default

SELECT COALESCE(discount, 0) AS discount FROM orders;

COALESCE(discount, 0) means "use discount, but if it's blank, use 0 instead." It's the standard way to turn "unknown" into a real business default before you display or add things up.

The interview tip: blanks sort to the end

By default in Postgres, blank values sort last. If you're building a "needs attention" list of orders that haven't shipped (shipped date is blank), you probably want them first, so say so explicitly with ORDER BY shipped_at NULLS FIRST.

---

Next: Subqueries vs CTEs →