Lesson 6 of 26 · see the practice database

NULL: missing values

Sometimes a value is simply not known. A customer did not give an email address. An employee at the top has no manager. SQL marks such a gap with NULL. NULL is not zero and it is not empty text. It means "there is no value here". It behaves in ways that surprise every beginner, so it gets a lesson to itself.

Seeing NULL

Three of our customers have no email address.

SELECT name, email FROM customers;

The trap: = NULL finds nothing

The natural thing to write is email = NULL. It runs without an error and returns no rows.

SELECT name FROM customers WHERE email = NULL;

Why? NULL means "unknown". Is an unknown value equal to another unknown value? The database cannot say yes and cannot say no, so the answer is itself unknown, and WHERE keeps only the rows where the answer is a definite yes. Any comparison with NULL using =, <>, < or > comes out unknown.

IS NULL and IS NOT NULL

SQL has a special test for the purpose.

SELECT name FROM customers WHERE email IS NULL;

SELECT name, email FROM customers WHERE email IS NOT NULL;

Use IS NULL and IS NOT NULL, always. Never = NULL.

SELECT name, department FROM employees WHERE manager_id IS NULL;

That finds the person at the top, who reports to nobody.

NULL hides from "not equal" too

Here is the second trap. Suppose you ask for employees whose manager is not number 1.

SELECT name, manager_id FROM employees WHERE manager_id <> 1;

The employee with no manager is missing from the result. For that row the test NULL <> 1 is unknown, not true, so the row is dropped. If you want such rows, ask for them.

SELECT name, manager_id
FROM employees
WHERE manager_id <> 1 OR manager_id IS NULL;

Whenever you filter on a column that can hold NULL, stop and ask what should happen to the rows with no value.

NULL in calculations

Any arithmetic with NULL gives NULL. Unknown plus five is unknown.

SELECT 5 + NULL AS sum, 'Hello ' || NULL AS text;

One missing value can wipe out a whole calculated column, so you need a way to put something in its place.

COALESCE: use this if that is missing

COALESCE takes a list of values and returns the first one that is not NULL.

SELECT name, COALESCE(email, 'no email given') AS email
FROM customers;

It works in every major database. You can give it more than two values, and it walks along them until it finds one that exists.

SELECT COALESCE(NULL, NULL, 'third') AS first_real_value;

Use it in calculations to treat a missing number as zero, but only when zero is honestly what a missing value means.

NULL in sorting and counting

When you sort, the rows with NULL are grouped together at one end. In SQLite and MySQL they come first in an ascending sort. In PostgreSQL and Oracle they come last. The lesson on ORDER BY comes next.

Counting treats NULL specially too. COUNT(*) counts rows. COUNT(email) counts only the rows where email has a value.

SELECT COUNT(*) AS customers, COUNT(email) AS with_email
FROM customers;

The difference between the two numbers is the number of missing emails. Counting is covered properly in the lesson on aggregate functions.

NULL and NOT IN

A NOT IN list that contains a NULL returns no rows at all, for the same "unknown" reason. This matters most when the list comes from another query, as in the lesson on subqueries. Keep it in mind as a place to look when a NOT IN gives an empty answer.

Empty text is not NULL

A column can hold text with nothing in it, written ''. That is a value: the text is known and it is empty. IS NULL does not find it. (Oracle is the exception. It treats empty text as NULL.) When a form leaves a box blank, some programs save NULL and others save '', so real data often has both. To catch both:

SELECT name FROM customers WHERE email IS NULL OR email = '';

Why allow NULL at all?

Because the alternative is worse. If a missing price were stored as 0, the product would look free and would drag the average price down. NULL says honestly that the value is not known, and the functions that add and average simply leave it out.

Try it yourself

SELECT name FROM customers WHERE email IS NOT NULL;

SELECT name, manager_id FROM employees WHERE manager_id IS NOT NULL;

SELECT name, COALESCE(manager_id, 0) AS manager FROM employees;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.