Is NULL the same as zero or an empty string?

No. Zero is a known number and an empty string is a known (empty) text value. NULL means there is no value at all — it is unknown or missing. Because of that, NULL behaves differently in comparisons, filters, and aggregates.

Why does = NULL not work in SQL?

Any comparison with NULL returns "unknown" rather than true or false, so NULL = NULL is unknown — never true. To test for NULL you must use IS NULL or IS NOT NULL, which return a clean true/false.

What does COALESCE do?

COALESCE returns the first non-NULL value from its list of arguments. It is the standard way to provide a fallback for missing data, such as COALESCE(phone, email, 'No contact') or treating a missing number as zero with COALESCE(discount, 0).

Beginner

SQL NULL Handling: IS NULL, COALESCE & NULLIF

NULL is not zero, and it is not an empty string — it is the absence of a value, and it follows its own logic. Master the rules that quietly break queries and interviews.

NULL is the most misunderstood value in SQL — partly because it is not really a value at all. It represents the absence of data: unknown, missing, or not applicable. Because of that, it follows three-valued logic (true, false, and unknown), which quietly changes how comparisons, filters, and aggregates behave. Getting NULL right is the difference between a report that is correct and one that is subtly wrong. Keep the SQL cheat sheet open as you work through it.

What is NULL?

NULL is not zero, and it is not an empty string. Zero is a known number; an empty string is a known (empty) text value; NULL means there is no value at all. The key consequence: any comparison with NULL returns "unknown", not true or false.

-- All of these return NULL (unknown), not true/false
SELECT
  NULL = NULL    AS a,   -- NULL
  NULL = 1       AS b,   -- NULL
  NULL <> 1      AS c;   -- NULL

Three-valued logic — Comparisons involving NULL return UNKNOWN — never true or false. WHERE, ON, and HAVING keep only TRUE rows, so UNKNOWN rows quietly disappear.

Testing for NULL

To check whether a value is missing, use IS NULL and IS NOT NULL. These are the only operators that return a clean true/false for NULL.

-- Users with no phone on file
SELECT name
FROM users
WHERE phone IS NULL;

-- Orders that have shipped
SELECT id
FROM orders
WHERE shipped_at IS NOT NULL;

COALESCE — Default Values

COALESCE returns the first non-NULL argument from a list. It is the standard way to supply a fallback for missing data — a default label, a backup contact, or zero instead of NULL.

-- Use phone, fall back to email, then to a literal
SELECT
  name,
  COALESCE(phone, email, 'No contact') AS contact
FROM users;

-- Treat missing discounts as 0 in a calculation
SELECT id, total - COALESCE(discount, 0) AS net
FROM orders;

NULLIF — Guarding Division

NULLIF returns NULL if its two arguments are equal, otherwise the first argument. Its most common job is preventing division-by-zero errors by turning a zero denominator into NULL (and any division by NULL is simply NULL).

-- Avoid "division by zero" when quantity is 0
SELECT
  id,
  total / NULLIF(quantity, 0) AS unit_price
FROM order_items;

NULL in Aggregates

Aggregate functions ignore NULLs — with one famous exception. COUNT(*) counts rows; COUNT(column) counts only non-NULL values in that column. SUM and AVG skip NULLs entirely, which can surprise you when computing averages.

SELECT
  COUNT(*) AS total_rows,    -- counts every row
  COUNT(phone) AS has_phone, -- skips NULL phones
  AVG(rating) AS avg_rating  -- NULL ratings ignored, not treated as 0
FROM reviews;

NULL in JOINs

NULLs appear naturally on the unmatched side of an outer join — that is the whole point of a LEFT JOIN. Testing for those NULLs is how you find rows with no match, like customers who have never ordered.

-- Users who have never placed an order
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Note that two NULLs do not match in a JOIN condition either — ON a.x = b.x excludes rows where either side is NULL, for the same reason NULL = NULL is unknown.

Common Mistakes & Tips

Using = NULL or <> NULL instead of IS NULL / IS NOT NULL
Forgetting that WHERE drops "unknown" rows — a NOT IN with a NULL in the list can return nothing
Assuming AVG treats NULL as zero — it skips NULLs entirely
Concatenating with NULL — in standard SQL, 'a' || NULL is NULL; use COALESCE first
Confusing NULL with an empty string '' — they are different values

NULL behaviour is a favourite interview trap — see it alongside other gotchas in the top SQL interview questions, then practice on real nullable data.