Lesson 2 of 14 · 7 min · Querying one table

NULL: missing values and how SQL treats them

Covers: PostgreSQL 18 syntax; standard SQL that also runs on MySQL 8. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • What NULL means and what it does not mean
  • Why = NULL never works and what to use instead
  • Three-valued logic: true, false and unknown
  • COALESCE and NULLIF
  • How COUNT, SUM and AVG treat NULLs, and the NOT IN trap

In simple words

NULL is not zero and it is not an empty text. It means "we do not know this value". If you ask "is this unknown thing equal to Hyderabad?", the honest answer is not yes or no, it is "unknown". SQL follows that logic, so comparisons with NULL give "unknown", and rows with an "unknown" answer are left out of results. That surprises everyone once.

Words to know

  • NULL: a marker for a missing or unknown value.
  • Unknown: the third result of a test, besides true and false.
  • COALESCE: a function that picks the first value that is not NULL.
  • Aggregate: a function such as COUNT or SUM that summarises many rows.

NULL is not a value

In the practice data, Divya has no city (NULL).

SELECT name FROM customers WHERE city = NULL;

This returns no rows, even for Divya. city = NULL evaluates to unknown, not true. The right test is:

SELECT name FROM customers WHERE city IS NULL;     -- Divya
SELECT name FROM customers WHERE city IS NOT NULL; -- the other four

Some databases offer a null-safe equal (IS NOT DISTINCT FROM in PostgreSQL, <=> in MySQL) for comparing two columns that may both be NULL.

Three-valued logic

Every comparison with NULL is unknown, and WHERE keeps only rows where the test is true.

Expression Result
NULL = NULL unknown
NULL <> 'x' unknown
TRUE AND unknown unknown
FALSE AND unknown false
TRUE OR unknown true
NOT unknown unknown

A consequence you must remember:

SELECT name FROM customers WHERE city <> 'Hyderabad';

returns Ravi and Meena only. Divya is not returned, because NULL <> 'Hyderabad' is unknown. If you want her too:

WHERE city <> 'Hyderabad' OR city IS NULL

COALESCE and NULLIF

COALESCE(a, b, c) returns the first argument that is not NULL. Use it to give a missing value a default for display or calculation:

SELECT name, COALESCE(city, 'Unknown') AS city FROM customers;

Divya now shows Unknown. This does not change the stored data, only the result.

NULLIF(a, b) returns NULL when the two are equal, otherwise a. Its best use is avoiding division by zero:

SELECT revenue / NULLIF(orders_count, 0) AS revenue_per_order FROM daily_stats;

When orders_count is 0 the division becomes revenue / NULL, which is NULL instead of an error.

CASE can do the same job with more control: CASE WHEN city IS NULL THEN 'Unknown' ELSE city END.

Aggregates and NULL

Aggregate functions ignore NULLs, except COUNT(*), which counts rows.

SELECT COUNT(*) AS total_rows, COUNT(city) AS with_city FROM customers;
 total_rows | with_city
------------+-----------
          5 |         4
  • COUNT(*) counts rows (5); COUNT(city) counts non-NULL cities (4).
  • SUM and AVG skip NULLs. The average of 10, 20 and NULL is 15, not 10, because only two values count. If a missing value should count as zero, say so: AVG(COALESCE(x, 0)).
  • SUM of a column that is NULL in every row is NULL, not 0. Wrap with COALESCE(SUM(x), 0) for reports.

Sorting and grouping with NULL

  • ORDER BY: PostgreSQL puts NULLs last in ascending order and first in descending order; MySQL puts them first in ascending order. Control it with NULLS FIRST or NULLS LAST in PostgreSQL.
  • GROUP BY puts all NULLs into one group.
  • DISTINCT treats all NULLs as the same value.

The NOT IN trap

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

This works here, because orders.customer_id has no NULLs. Kiran and Divya are returned. But if even one order had a NULL customer_id, the subquery list would contain NULL and NOT IN would return no rows at all: each comparison id <> NULL is unknown. Two safe ways:

-- NOT EXISTS ignores the problem
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- or filter NULLs out of the list
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)

Prefer NOT EXISTS (or a left join, next lessons) for "rows with no match".

Common mistakes

  • Using = NULL or <> NULL.
  • Forgetting that <> 'x' drops NULL rows.
  • Assuming AVG counts missing values as zero.
  • NOT IN with a subquery that can return NULL.
  • Storing "N/A" or empty text instead of NULL, which breaks numeric columns and aggregates.

Try it yourself

  1. Write the query that finds customers with no city, then the one that finds customers whose city is not Hyderabad including the unknown ones.
  2. Show every customer with Unknown where the city is missing.
  3. Compare COUNT(*) and COUNT(city); explain the difference.
  4. Rewrite the NOT IN query with NOT EXISTS.

Check yourself

Why does WHERE city = NULL return nothing?

Comparing anything with NULL gives unknown, and WHERE keeps only true rows. Use IS NULL.

What do COUNT(*) and COUNT(column) return for a column with some NULLs?

COUNT(*) counts all rows. COUNT(column) counts only rows where that column is not NULL.

What does COALESCE(city, 'Unknown') do?

It returns the city, or 'Unknown' when the city is NULL.

How do you avoid a divide-by-zero error in a ratio?

Divide by NULLIF(denominator, 0), which turns a zero denominator into NULL so the result is NULL, not an error.

Interview questions

What is the difference between NULL, zero and an empty string?

NULL means the value is unknown or missing. Zero is a known number. An empty string is a known text of length zero. They compare differently and aggregate differently.

Why can NOT IN return no rows?

If the list or subquery contains a NULL, every x <> NULL comparison is unknown, so no row is ever true. NOT EXISTS avoids this.

Does AVG include NULL values?

No. It averages only non-NULL values, so the count in the denominator is the number of non-NULL rows.

Summary

  • NULL means unknown; test with IS NULL and IS NOT NULL, never =.
  • A comparison with NULL is unknown and WHERE drops those rows, including with <>.
  • COALESCE supplies defaults and NULLIF prevents division by zero.
  • Aggregates skip NULLs except COUNT(*); prefer NOT EXISTS over NOT IN with subqueries.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.