Lesson Beginner

NULL (IS NULL, COALESCE)

Tip

NULL is your friend and NULL is your enemy. Know the difference.

NULL means missing or unknown. It is not zero. It is not an empty string. It is not false.
It is the absence of a value, and SQL treats it differently from every other value you will work with.

When you handle NULL deliberately, it protects your queries from lying to you. When you ignore it, it quietly breaks your joins, skews your averages, and hides rows you thought you were counting.

Why NULL Needs Its Own Rules #

Any comparison with NULL returns UNKNOWN, not TRUE or FALSE.
That is why = NULL never matches rows, even rows where the value really is NULL.

SQL gives you specific operators to work with NULL safely:

IS NULL / IS NOT NULL to test for missing values
COALESCE to substitute a default when a value is missing

IS NULL and IS NOT NULL #

Use IS NULL and IS NOT NULL to filter rows by whether a column has a value.

sql

-- Rows where total was never recorded
SELECT id, user_id
FROM orders
WHERE total IS NULL;

-- Rows where we do have a total
SELECT id, user_id, total
FROM orders
WHERE total IS NOT NULL;

Warning

WHERE column = NULL returns zero rows, even if the column really is NULL.
The correct filter is WHERE column IS NULL. This trips up almost every SQL learner once.

COALESCE: Supply a Default #

COALESCE(a, b, c) returns the first argument that is not NULL.
Use it to display a fallback value instead of an empty cell.

sql

SELECT
  id,
  COALESCE(total, 0) AS total_or_zero
FROM orders;

You can chain COALESCE arguments. COALESCE(preferred, backup, 'unknown') checks each in order and returns the first non-null value.

NULL in Aggregates #

Aggregate functions have their own NULL behavior:

COUNT(*) counts every row, including rows with NULL values.
COUNT(column) counts only rows where that column is not NULL.
SUM, AVG, MIN, MAX ignore NULL entirely.

This difference matters. If 100 orders have a discount column and 40 of them are NULL, AVG(discount) averages over 60 rows, not 100.

Try It #

The seeded orders table has no real NULL values, so this query synthesizes them: cancelled orders are treated as if their total were missing. Compare the three output columns row by row to see how IS NULL and COALESCE behave.

Loading SQL editor...

Mistakes to Watch For #

Using = NULL or != NULL instead of IS NULL / IS NOT NULL.
Assuming NULL is the same as 0 or '' in arithmetic or string operations. NULL + 1 is NULL, not 1.
Forgetting that AVG and SUM ignore NULL, which changes the denominator.
Mixing COUNT(*) and COUNT(column) without knowing which one you need.
Joining on a column that contains NULL, since those rows silently drop from inner joins.

Knowledge check #

5 questions

0 / 5 answered

WHERE col = NULL returns:
COALESCE(a, b, c, 0) returns:
NULLIF(a, b) returns:
For aggregates like SUM(amount), what happens to NULL values?
You want "everything except status = 'cancelled', including rows where status is NULL." The correct WHERE is:

Next Step #

Continue to COUNT / SUM / AVG / MIN / MAX to start the Aggregation section — where the NULL rules you just learned change what your numbers mean.