Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 125 ยท Querying Data

Working with NULL

Understand NULL in SQL. Learn three-valued logic, IS NULL, how NULL behaves in comparisons, NOT IN, sorting and aggregates, and how IS DISTINCT FROM helps.

Real data has gaps. A new hire has no manager yet, a project has no end date, a student hasn't received a grade. SQL marks every such gap with a special marker called NULL. It means "there is no value here": not zero, not an empty string, not false.

NULL causes more surprising query results than any other feature of SQL, because it doesn't behave like a normal value. A condition such as salary > 50000 can be true, false or unknown. This lesson explains that third state, the rules that follow from it, and the handful of habits that keep NULL from silently dropping rows from your results.

What NULL means

Look at the company's projects table. Some projects have no budget recorded and some have no end date yet:

SQL NULL in a table ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The Quarterly Forecasting project has a NULL budget, and five projects have a NULL end date. The result table shows the word NULL, but the column doesn't contain the text "NULL"; it contains nothing at all.

NULL can mean different things depending on the column: unknown (a birth year nobody recorded), not applicable (the CEO has no manager) or not yet (a project that hasn't ended). SQL doesn't distinguish these meanings, so it's your job to know what a NULL means in each column. When a column must always have a value, the table designer declares it NOT NULL; you'll do that in Constraints.

Three-valued logic

Because NULL means "no value", SQL can't decide whether NULL = 5 is true. Is the missing budget 5? Maybe, maybe not. So the answer is a third truth value, unknown, which SQL also writes as NULL. The same goes for arithmetic and string concatenation: if one input is missing, the result is missing.

Run this query. Every column compares or combines something with NULL:

SQL NULL in expressions

Press Run (or Ctrl + Enter) to see the result here.

Almost everything is NULL, even NULL = NULL: two unknown values aren't known to be equal. The two exceptions are worth remembering: NULL AND false is false (0), because AND is false as soon as one side is false, and NULL OR true is true (1), because OR is true as soon as one side is true.

Here are the full rules, with ? for unknown:

AND true false ?
true true false ?
false false false false
? ? false ?
OR true false ?
true true true true
false true false ?
? true ? ?

And NOT unknown is still unknown.

The rule that matters most: WHERE keeps a row only when the condition is true. Rows where it is false or unknown are dropped. That's why a NULL can make rows disappear without any error.

IS NULL and IS NOT NULL

Since = NULL is never true, SQL has dedicated operators for testing missing values: IS NULL and IS NOT NULL. They always return true or false, never unknown.

In the company database, the CEO and a contractor have no department. Compare the wrong way and the right way:

SQL IS NULL versus = NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns nothing, the second returns Kofi Okafor (the CEO) and Amara Ivanova (the contractor), and the count shows that the other 34 employees belong to a department.

= NULL is not an error. The database runs it happily and returns zero rows, which makes this bug easy to miss. Whenever you compare with NULL, write IS NULL or IS NOT NULL.

NULL hides rows in negative conditions

The most common NULL bug is a condition that looks like it covers everything. Every enrollment either has a grade of at least 50 or it doesn't, right? Not when the column can be NULL. The school's enrollments table has grades that haven't been entered yet. Count each group:

SQL Rows that match neither condition ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

There are 299 enrollments, but 239 passed plus 36 not passed is only 275. The missing 24 are exactly the no_grade_yet rows: for those rows grade >= 50 is unknown, and NOT unknown is unknown too, so they fall into neither group. When you mean "everyone who hasn't passed, including those without a grade", say it:

SQL
WHERE grade < 50 OR grade IS NULL

The NOT IN trap

NOT IN is a chain of <> comparisons joined with AND. id NOT IN (1, 2, NULL) means id <> 1 AND id <> 2 AND id <> NULL, and the last part is always unknown. The whole condition can therefore never be true:

SQL NOT IN with a NULL in the list ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns four departments, the second returns none. With a literal list this is easy to spot, but the same thing happens with a subquery such as NOT IN (SELECT department_id FROM employees) whenever the subquery returns even one NULL. You saw this briefly in IN and BETWEEN; the safe alternative, NOT EXISTS, is covered in EXISTS.

NULL in sorting

NULL can't be compared, so every database simply picks a place for it when sorting. They don't agree:

Database NULLs in ORDER BY x (ascending) NULLS FIRST / NULLS LAST
SQLite first supported (3.30+)
MySQL / MariaDB first not supported
SQL Server first not supported
PostgreSQL last supported
Oracle last supported

With DESC the order flips in every database. When the position matters, say so with NULLS FIRST or NULLS LAST:

SQL Sorting NULL values ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In the first result the project with no budget comes first; in the second, the largest budgets come first and the missing one is pushed to the end. In databases without NULLS LAST, sort by a test first: ORDER BY budget IS NULL, budget DESC (the test is 0 for real values and 1 for NULL). Sorting with ORDER BY covers this in more depth.

NULL in aggregates

Aggregate functions summarize many rows into one value. They follow a simple rule: they ignore NULLs. COUNT(*) counts rows, while COUNT(column) counts only the rows where that column has a value:

SQL Aggregates skip NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

There are 7 projects but only 6 have a budget. The average divides the total of 1,305,000 by 6, not 7, giving 217,500. That's usually what you want, but be aware of it: if a missing budget really means zero, the "true" average would be lower. You can make that choice explicit with COALESCE(budget, 0), the subject of COALESCE, IFNULL and NULLIF.

One more surprise: on a set with no values at all, SUM returns NULL, not 0. count(*) and count(column) always return a number. You'll see all of this again in Aggregate Functions.

Comparing values that might be NULL

Sometimes you want to treat two NULLs as equal, for example when checking whether a value changed between two versions of a row. old = new is unknown when either side is NULL. The standard solution is IS DISTINCT FROM, a comparison that treats NULL as an ordinary value and never returns unknown:

SQL IS DISTINCT FROM

Press Run (or Ctrl + Enter) to see the result here.

Two NULLs are not distinct (0), 5 is distinct from NULL (1), and 5 is not distinct from 5 (1). SQLite also lets you use plain IS and IS NOT with any value, as the last column shows: a IS b is SQLite's shorthand for a IS NOT DISTINCT FROM b.

Database Null-safe "equal" Null-safe "not equal"
Standard SQL, PostgreSQL, SQLite 3.39+, SQL Server 2022+ a IS NOT DISTINCT FROM b a IS DISTINCT FROM b
SQLite (any version) a IS b a IS NOT b
MySQL / MariaDB a <=> b NOT (a <=> b)

Where NULLs are treated as equal

Although NULL = NULL is unknown, a few SQL features group NULLs together on purpose:

  • DISTINCT keeps a single NULL (see DISTINCT).
  • GROUP BY puts all NULLs into one group (see GROUP BY).
  • UNION, INTERSECT and EXCEPT treat NULLs as duplicates of each other.

But a UNIQUE constraint allows several NULLs in most databases, because unknown values aren't known to be duplicates. The company's employees.email column is UNIQUE, yet three employees have no email. (SQL Server is the exception: its UNIQUE allows only one NULL.)

NULL is not an empty string, except in Oracle. In most databases '' is a real, known value of length zero, and '' IS NULL is false. Oracle stores '' as NULL, so code that relies on the difference behaves differently there.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SELECT 10 + NULL; return?

    Any arithmetic with a missing value produces a missing value, so the result is NULL.
  2. 2. The employees table has 36 rows, and 2 have a NULL department_id. How many rows does WHERE department_id <> 3 return if 4 employees are in department 3?

    The 2 NULL rows make department_id <> 3 unknown, so they're dropped along with the 4 rows in department 3: 36 โˆ’ 4 โˆ’ 2 = 30.
  3. 3. Which condition finds projects with no end date?

    Only IS NULL tests for a missing value. = NULL is never true, and '' is a real (empty) value.
  4. 4. A budget column holds 100, 200 and NULL. What do count(*), count(budget) and avg(budget) return?

    count(*) counts rows (3), count(budget) counts values (2), and avg ignores the NULL: (100 + 200) / 2 = 150.
  5. 5. Why does WHERE id NOT IN (1, NULL) return no rows?

    NOT IN expands to id <> 1 AND id <> NULL. The second part is unknown, so the whole condition is false or unknown for every row.

Summary

  • NULL marks a missing value. It isn't zero, an empty string or false.
  • Comparisons and arithmetic with NULL produce unknown (NULL); WHERE keeps only rows where the condition is true.
  • Test for missing values with IS NULL / IS NOT NULL; = NULL silently matches nothing.
  • Negative conditions (<>, NOT, NOT IN) skip NULL rows; a NULL inside a NOT IN list makes it match nothing.
  • NULLs sort first in SQLite, MySQL and SQL Server and last in PostgreSQL and Oracle; use NULLS FIRST / NULLS LAST where supported.
  • Aggregates ignore NULLs: count(*) counts rows, count(col) counts values, avg divides by the number of values.
  • IS DISTINCT FROM (SQLite: IS NOT, MySQL: <=>) compares values while treating NULLs as equal.

Next: Sorting with ORDER BY, where you'll control the order of your results in detail.