SQL NULL Values

Understand what NULL means, test for it with IS NULL, replace it with COALESCE, and avoid the surprises NULL causes in comparisons, counts and NOT IN.

Lesson 4 of 102Beginner17 min read

Real data is rarely complete. A customer skips the optional phone number, a product hasn't been given a weight yet, an order hasn't been shipped so it has no delivery date. Databases mark these gaps with a special marker called NULL.

NULL doesn't mean zero, and it doesn't mean an empty piece of text. It means "unknown" or "missing". That small idea has big consequences: a comparison with an unknown value can't be true or false, so it's unknown too. Many confusing query results, such as rows that seem to vanish or totals that don't add up, come from NULL.

In this lesson you'll learn how NULL behaves in comparisons, calculations and counts, how to test for it properly, how to replace it with a sensible default, and one trap with NOT IN that catches even experienced developers.

Meet NULL

For this lesson, the customers table has an extra email column. Chloe and Ema haven't given one:

idnamecityemail
1AnaLisbonana@example.com
2BenLeedsben@example.com
3ChloeLyonNULL
4DevLeedsdev@example.com
5EmaPortoNULL

NULL is different from '' (an empty string) and from 0. An empty string means "we know there's no text"; NULL means "we don't know". Columns marked NOT NULL when the table is created can never hold it, as you'll see in the constraints lesson.

Testing for NULL

Because NULL is unknown, email = NULL doesn't give true or false. It gives NULL, and WHERE only keeps rows where the condition is true. So this query returns nothing, ever:

SQL
SELECT name FROM customers WHERE email = NULL;   -- always 0 rows

Use the special operators instead:

SQL
SELECT name FROM customers WHERE email IS NULL;      -- Chloe, Ema
SELECT name FROM customers WHERE email IS NOT NULL;  -- Ana, Ben, Dev

The same rule hides rows in other conditions. WHERE email <> 'ben@example.com' returns only Ana and Dev, because for Chloe and Ema the answer is unknown, not true.

NULL in calculations and logic

Almost any calculation involving NULL gives NULL: 5 + NULL is NULL, and so is 'Hi ' || NULL. SQL uses three-valued logic: true, false and unknown.

ExpressionResult
NULL = NULLNULL (unknown)
NULL OR TRUETRUE
NULL AND FALSEFALSE
NULL AND TRUENULL

When one side already decides the answer, as with OR TRUE or AND FALSE, the unknown doesn't matter. Otherwise, the result stays unknown.

COALESCE, counts and sorting

COALESCE(a, b, ...) returns the first value that isn't NULL, which is the standard way to show a default: COALESCE(email, 'not given').

Aggregate functions skip NULLs. COUNT(*) counts rows, but COUNT(email) counts only rows where email has a value. SUM and AVG ignore NULLs too, so an average is taken over the known values only.

When sorting, SQLite and MySQL put NULLs first in ascending order, while PostgreSQL puts them last. PostgreSQL and SQLite let you choose with ORDER BY email NULLS LAST.

SQL NULL Syntax

NULL handling

WHERE column IS NULL
WHERE column IS NOT NULL
COALESCE(column, 'default')
COUNT(*)          -- every row
COUNT(column)     -- rows where column isn't NULL
ORDER BY column NULLS LAST    -- PostgreSQL, SQLite

SQL NULL Summary

  • NULL means unknown or missing; it isn't 0 or an empty string.
  • Test with IS NULL and IS NOT NULL; = NULL never matches anything.
  • Comparisons and calculations with NULL usually give NULL, and WHERE drops those rows.
  • COUNT(column), SUM and AVG skip NULLs; COUNT(*) counts every row.
  • COALESCE(value, default) replaces NULL with a value of your choice.

SQL NULL Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Showing a default with COALESCE

SQL
SELECT name,
       COALESCE(email, 'not given') AS email
FROM customers;
Output
name  | email
------+----------------
Ana   | ana@example.com
Ben   | ben@example.com
Chloe | not given
Dev   | dev@example.com
Ema   | not given
Line-by-line explanation
  1. Line 2Where email is NULL, COALESCE uses the second value instead. The result column keeps the name email thanks to AS.
Example 2

Counting missing values

SQL
SELECT COUNT(*)                AS customers,
       COUNT(email)            AS with_email,
       COUNT(*) - COUNT(email) AS missing
FROM customers;
Output
customers | with_email | missing
----------+------------+--------
5         | 3          | 2
Line-by-line explanation
  1. Line 1COUNT(*) counts all five rows.
  2. Line 2COUNT(email) skips the two NULLs.
  3. Line 3The difference is the number of customers with no email, a quick data-quality check.
Example 3

Why = NULL doesn't work

SQL
SELECT name,
       email = NULL  AS equals_null,
       email IS NULL AS is_null
FROM customers;
Output
name  | equals_null | is_null
------+-------------+--------
Ana   | NULL        | 0
Ben   | NULL        | 0
Chloe | NULL        | 1
Dev   | NULL        | 0
Ema   | NULL        | 1
Line-by-line explanation
  1. Line 2Comparing anything with NULL using = gives NULL for every row, even where email really is missing.
  2. Line 3IS NULL gives a proper answer: 1 means true and 0 means false in SQLite and MySQL.

Common SQL NULL Mistakes

Using = NULL

Don't
SELECT * FROM customers WHERE email = NULL;
Do
SELECT * FROM customers WHERE email IS NULL;

Forgetting NULLs in a "not equal" filter

Don't
-- "everyone except Ben" but Chloe and Ema disappear
SELECT name FROM customers WHERE email <> 'ben@example.com';
Do
SELECT name FROM customers
WHERE email <> 'ben@example.com' OR email IS NULL;

NOT IN with a list that contains NULL

Don't
-- returns no rows at all if any manager_id is NULL
SELECT name FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff);
Do
SELECT name FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff WHERE manager_id IS NOT NULL);

x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and the second part is always unknown.

SQL NULL in Real Projects

NULL in real data

Analysts check for NULLs first when they receive a new dataset, because missing values distort averages and counts. Reports use COALESCE to show "n/a" or 0 instead of blank cells. Shipping systems use NULL delivery dates to find orders that haven't left the warehouse, with a simple WHERE shipped_at IS NULL.

SQL NULL FAQ

What is the difference between COALESCE, IFNULL and ISNULL?

COALESCE(a, b, c) is standard SQL and returns the first non-NULL value from any number of arguments, so it works in every major database. IFNULL in MySQL and SQLite, ISNULL in SQL Server and NVL in Oracle do the same job for exactly two values. Prefer COALESCE for portable queries.

Does COUNT include NULL values?

COUNT(*) counts every row, including rows with NULLs. COUNT(column) counts only rows where that column isn't NULL. So on a table of five customers where two have no email, COUNT(*) is 5 and COUNT(email) is 3. SUM, AVG, MIN and MAX also ignore NULLs.

Where do NULLs appear when sorting?

It depends on the database. PostgreSQL puts NULLs last in ascending order and first in descending order; MySQL and SQLite put them first in ascending order. PostgreSQL and SQLite let you choose with ORDER BY email NULLS LAST; elsewhere, sort by email IS NULL first.

Can a column with a UNIQUE constraint contain several NULLs?

In PostgreSQL, MySQL and SQLite, yes, because NULL isn't considered equal to another NULL, so several rows can have a missing value. SQL Server allows only one NULL in a unique column unless you use a filtered index. A primary key column can never be NULL.

Should I store missing values as NULL, an empty string or zero?

Use NULL when the value is unknown or not applicable, such as an email nobody has provided. Use 0 or '' only when they're real values: a stock level of zero is known, not missing. Mixing them makes counts and averages misleading, because 0 is included in AVG and NULL isn't.

SQL NULL Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query to find customers with no email address.

SELECT name FROM customers WHERE email ;
Multiple choiceeasy

What does NULL mean in a database?

Predict the outputmedium

A column bonus holds 100, NULL and 200. What does SELECT COUNT(bonus) FROM staff; return?

Predict the outputmedium

What does SELECT COALESCE(NULL, NULL, 'x', 'y'); return?

Multiple choicehard

Using the same bonus values (100, NULL, 200), what does AVG(bonus) return?

SQL NULL Quiz

5 questions to check what you learned.

  1. Which condition finds rows where phone has a value?

    • phone != NULL
    • phone <> ''
    • phone IS NOT NULL
    • NOT phone = NULL

  2. What is 10 + NULL?

    • NULL
    • 10
    • 0
    • An error

  3. What's the difference between COUNT(*) and COUNT(email)?

    • None
    • COUNT(*) counts all rows; COUNT(email) counts rows where email isn't NULL
    • COUNT(email) counts unique emails
    • COUNT(*) skips NULLs

  4. What does NULL OR TRUE evaluate to?

    • NULL
    • FALSE
    • TRUE
    • An error

  5. Why can WHERE id NOT IN (subquery) return no rows unexpectedly?

    • Subqueries can't be used with NOT IN
    • If the subquery returns a NULL, every NOT IN comparison becomes unknown
    • NOT IN only works with numbers
    • The subquery runs once per row

Related SQL topics

03WHERE (Filtering)Return only the rows you need with comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.06FunctionsTransform values inside a query with string, number, date and NULL-handling functions.15Subqueries & CTEsUse the result of one query inside another with subqueries, IN, EXISTS, derived tables and WITH clauses.
Esc
↑↓ to navigateEnter to open