Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 9 of 49

SQL NULL: missing values

Understand what NULL means, why = NULL never works, how to test with IS NULL and IS NOT NULL, and how NULL affects comparisons, arithmetic and counts.

13 minFiltering and sortingUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonTest for missing values with IS NULL and IS NOT NULL, and predict how NULL changes comparisons, arithmetic and COUNT.

What you will be able to do

After this lesson you can find rows with missing values, avoid the classic = NULL mistake, and understand why some rows quietly vanish from your results. NULL is the most common source of "my query looks right but the numbers are off", so it is worth an entire lesson.

What NULL means

NULL marks a missing or unknown value. It is not zero, and it is not an empty piece of text. It says "there is no value here". The sample database has several of them, and each has a reason:

  • manager_id is NULL for people who report to nobody, such as the directors.
  • department_id is NULL for Ravi Pillai, a consultant who belongs to no department.
  • end_date is NULL for projects that have not finished.

The meaning of a NULL comes from the table design. A NULL end_date means "not ended yet", but in another table a NULL might mean "not recorded". Always check what a NULL stands for before you act on it.

Testing for NULL

You cannot use = to look for NULL. Try it and see:

sql
SELECT first_name, manager_id
FROM employees
WHERE manager_id = NULL;
result
(no rows returned)

Nothing comes back, and no error either. That is because manager_id = NULL is not true or false for any row, it is unknown: is an unknown value equal to an unknown value? Nobody can say. Even NULL = NULL is unknown. The correct tests are IS NULL and IS NOT NULL:

sql
SELECT first_name, manager_id
FROM employees
WHERE manager_id IS NULL;
result
+------------+------------+
| first_name | manager_id |
+------------+------------+
| Meera      | NULL       |
| Vikram     | NULL       |
| Dev        | NULL       |
| Lata       | NULL       |
| Pooja      | NULL       |
| Ravi       | NULL       |
+------------+------------+

Six people have no manager. Five of them are heads of departments and the sixth is Ravi, the consultant. To ask the opposite question, use IS NOT NULL:

sql
SELECT name, end_date
FROM projects
WHERE end_date IS NOT NULL;
result
+-----------------+------------+
|      name       |  end_date  |
+-----------------+------------+
| Customer Portal | 2024-09-30 |
| Spring Campaign | 2025-04-30 |
| Q3 Sales Push   | 2024-09-30 |
+-----------------+------------+

Three-valued logic

Because of NULL, a condition can have three outcomes: true, false and unknown. WHERE keeps rows only when the result is true. A row whose condition is unknown is treated just like a row where it is false and is left out.

Here is how that bites. This query asks for everybody not in department 1:

sql
SELECT first_name, department_id
FROM employees
WHERE department_id <> 1;
result
+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Vikram     |             2 |
| Neha       |             2 |
| Sameer     |             2 |
| Tara       |             2 |
| Dev        |             3 |
| Anika      |             3 |
| Yash       |             3 |
| Lata       |             4 |
| Omar       |             4 |
| Pooja      |             5 |
| Karan      |             5 |
| Sana       |             5 |
| Aman       |             2 |
+------------+---------------+

There are 14 employees outside department 1 in the table, counting Ravi, but only 13 appear. Ravi's department is NULL, so NULL <> 1 is unknown and his row is dropped. If you want him as well, say so:

sql
SELECT first_name, department_id
FROM employees
WHERE department_id <> 1 OR department_id IS NULL;
result
+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Vikram     |             2 |
| Neha       |             2 |
| Sameer     |             2 |
| Tara       |             2 |
| Dev        |             3 |
| Anika      |             3 |
| Yash       |             3 |
| Lata       |             4 |
| Omar       |             4 |
| Pooja      |             5 |
| Karan      |             5 |
| Sana       |             5 |
| Ravi       | NULL          |
| Aman       |             2 |
+------------+---------------+

Think of NULL as "I do not know". If you do not know someone's department, you cannot claim that they are not in department 1.

Combining NULL with other conditions

AND, OR and NOT follow the same three-valued rules. A few useful facts: true OR unknown is true, false AND unknown is false, and NOT unknown is unknown. You can mix the NULL tests into normal conditions:

sql
SELECT first_name, manager_id
FROM employees
WHERE manager_id IS NULL AND department_id IS NOT NULL;
result
+------------+------------+
| first_name | manager_id |
+------------+------------+
| Meera      | NULL       |
| Vikram     | NULL       |
| Dev        | NULL       |
| Lata       | NULL       |
| Pooja      | NULL       |
+------------+------------+

That is the five department heads, without the consultant.

NULL in arithmetic and text

Any calculation involving NULL returns NULL:

sql
SELECT NULL + 5 AS plus_five;
result
+-----------+
| plus_five |
+-----------+
| NULL      |
+-----------+

Joining text with || also gives NULL when either side is NULL. The CONCAT function is where databases disagree: MySQL returns NULL if any argument is NULL, while PostgreSQL and SQL Server treat a NULL argument as empty text. This is why NULL handling needs a conscious decision whenever you build text or totals.

NULL in aggregates

Aggregate functions are the one place where NULLs are skipped rather than spreading. COUNT(*) counts rows, and COUNT(column) counts the rows where that column is not NULL:

sql
SELECT COUNT(*) AS all_rows,
       COUNT(manager_id) AS with_manager,
       COUNT(department_id) AS with_department
FROM employees;
result
+----------+--------------+-----------------+
| all_rows | with_manager | with_department |
+----------+--------------+-----------------+
|       20 |           14 |              19 |
+----------+--------------+-----------------+

Twenty employees, fourteen with a manager, nineteen with a department. AVG, SUM, MIN and MAX also skip NULLs. The average of a column is the sum of its known values divided by how many known values there are, not by the number of rows. The aggregate lesson covers this in more depth.

Replacing NULL when you display it

Often you want a friendlier value for reports. COALESCE(value, replacement) returns the first argument that is not NULL, and it works on all four databases. The lesson on CAST, COALESCE and NULLIF shows it in full. Preview:

sql
SELECT first_name, COALESCE(manager_id, 0) AS manager_or_zero
FROM employees
WHERE id IN (1, 2, 18);
result
+------------+-----------------+
| first_name | manager_or_zero |
+------------+-----------------+
| Meera      |               0 |
| Arjun      |               1 |
| Ravi       |               0 |
+------------+-----------------+

Using 0 as a stand-in is fine for display, but 0 is not a real manager id, so do not store it as one.

Differences between databases

  • IS NULL and IS NOT NULL are standard and work everywhere.
  • A null-safe equality, where two NULLs count as equal, is written <=> on MySQL and IS NOT DISTINCT FROM on PostgreSQL. SQLite uses IS. These are not portable, so most SQL uses IS NULL explicitly.
  • How NULL is shown depends on the tool. Some print the word NULL, and some leave the cell empty.
  • On Oracle, an empty string is stored as NULL. On the four databases of this course it is not.
  • The sort position of NULL differs, as the sorting lesson shows.

The MySQL course has a lesson on NULL values with MySQL-specific functions.

Common mistakes

  • = NULL or <> NULL. Both are always unknown. Use IS NULL and IS NOT NULL.
  • Forgetting NULL rows after a negative condition, such as <> or NOT IN.
  • Treating NULL as zero in sums and averages, or as an empty string in text.
  • Comparing COUNT(*) and COUNT(column) without noticing they differ because of NULLs.
  • Storing special markers like 0, -1 or 'N/A' instead of NULL. They look like real values and spoil calculations.

Exercise

List the projects that have not ended yet, showing name and start_date.

Show one possible answer
sql
SELECT name, start_date
FROM projects
WHERE end_date IS NULL;
result
+-------------------+------------+
|       name        | start_date |
+-------------------+------------+
| Mobile App        | 2024-05-01 |
| Billing Migration | 2025-01-10 |
+-------------------+------------+

Key points

  • NULL means a value is missing or unknown. It is not zero and not an empty string.
  • Use IS NULL and IS NOT NULL to test for it. = NULL never matches anything.
  • Any comparison with NULL gives unknown, and WHERE keeps only rows where the condition is true.
  • Arithmetic with NULL gives NULL, and aggregate functions such as AVG and COUNT(column) skip NULLs.
  • COUNT(*) counts rows, while COUNT(column) counts only rows where that column is not NULL.

Check yourself

1. Which query finds employees without a manager?
2. What is the value of NULL + 5?
3. The table has 20 rows and 14 of them have a manager_id. What does COUNT(manager_id) return?
4. WHERE department_id <> 1 is used on a table where one row has a NULL department_id. What happens to that row?
5. What does a NULL in end_date of the projects table tell us in this database?