Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 79 ยท The Relational Model

Nulls and Three-Valued Logic

Understand what NULL means in a database, how three-valued logic (true, false, unknown) works, and why NULL surprises you in comparisons, aggregates, joins and NOT IN.

Real data has gaps. A customer has not given a phone number. An employee has no manager because she is the CEO. A measurement has not been taken yet. Relational databases mark a missing value with a special marker, NULL. It is simple to store and surprisingly hard to reason about, because NULL is not a value like the others: it is a statement that "there is no value here", and comparisons with it do not produce true or false.

This lesson explains what NULL means, how SQL's three-valued logic (true, false, unknown) works, and where NULL changes the behavior of comparisons, filters, aggregates, joins, DISTINCT and constraints. It ends with the most famous trap, NOT IN. Every example runs live in SQLite, whose NULL rules follow the SQL standard in all the cases shown here.

NULL is not a value

Unknown The value exists, we do not know it.
Not applicable There is no such value for this row.
Not yet recorded It will be filled in later.

One marker covers all three meanings, which is part of the problem.

1 / 2

What NULL means

NULL is not zero, not an empty string and not the word "null". A salary of 0 is a fact (the person is unpaid). An empty string is a fact (the name is blank). NULL means "we have no fact". Typically it stands for one of three things, and SQL cannot tell you which:

  • Unknown: the value exists but is not known (a patient's birth date was not recorded).
  • Not applicable: no value can exist (a manager for the CEO).
  • Not yet available: it will be known later (the end date of a project in progress).

Because of that ambiguity, good designs limit NULLs: they use NOT NULL on columns that must always have a value, and split rarely-used optional attributes into their own tables.

Comparisons with NULL are UNKNOWN

When SQL compares anything with NULL using =, <, >, <>, and so on, the result is neither true nor false. It is UNKNOWN. Even NULL = NULL is unknown: two unknown values are not known to be equal.

SQL Comparisons with NULL return NULL (unknown)

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

SQLite shows UNKNOWN as an empty (NULL) cell. The comparisons with NULL are all unknown. Only IS NULL and IS NOT NULL give a definite answer, and they are the correct way to test for missing values. (SQL also has a null-safe comparison, IS NOT DISTINCT FROM, which treats two NULLs as equal; SQLite lets you write it as plain IS.)

Truth tables for AND, OR and NOT

Logical operators must now handle three values. The rule of thumb: UNKNOWN means "could be either", and you check whether the answer would be the same in both cases.

p q p AND q p OR q
TRUE UNKNOWN UNKNOWN TRUE
FALSE UNKNOWN FALSE UNKNOWN
UNKNOWN UNKNOWN UNKNOWN UNKNOWN

TRUE OR anything is true, because it does not matter what the unknown is. FALSE AND anything is false for the same reason. Otherwise the answer depends on the unknown, so it stays unknown. NOT UNKNOWN is still UNKNOWN.

You can generate the full tables from SQL itself, using 1 for true, 0 for false and NULL for unknown:

SQL The three-valued truth tables, generated

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

All nine combinations appear. Check the rows with an UNKNOWN: FALSE AND UNKNOWN is FALSE and TRUE OR UNKNOWN is TRUE, while TRUE AND UNKNOWN and FALSE OR UNKNOWN stay UNKNOWN.

How WHERE treats UNKNOWN

A WHERE clause keeps a row only when the condition is TRUE. Rows where it is FALSE or UNKNOWN are dropped. This is the root of most NULL surprises.

SQL A row with a NULL silently disappears

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

Intuition says that commission = 0 and commission <> 0 split the table into two halves. With NULLs there is a third group in neither half. The covered count is 2, not 4, and the last query is the correct way to say "everyone whose commission is not zero or missing".

NULL in aggregates, DISTINCT and grouping

  • Aggregates ignore NULLs. SUM, AVG, MIN, MAX and COUNT(column) skip NULL inputs. COUNT(*) counts rows, so it includes them. AVG divides by the number of non-NULL values, not by the number of rows.
  • If every input is NULL (or there are no rows), SUM, AVG, MIN and MAX return NULL, while COUNT returns 0.
  • DISTINCT and GROUP BY treat NULLs as equal to each other, which is a deliberate exception: all NULLs form one group.
  • ORDER BY puts NULLs together, but whether they come first or last depends on the engine. SQLite sorts them first in ascending order.
  • UNIQUE constraints allow several NULLs in most engines (SQLite, PostgreSQL, MySQL), because NULLs are never considered equal. SQL Server is a notable exception: it allows only one.
SQL NULL in aggregates, DISTINCT, GROUP BY and UNIQUE

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

The first query gives count_star 5, count_points 3, sum_points 35, avg_points about 11.67 and max_points 20. The GROUP BY shows one NULL group of two players next to the red group of three. There are two distinct teams (NULL and red), and all three accounts are stored.

NULL in joins

An equality join condition a.x = b.x is UNKNOWN when either side is NULL, so rows with NULL keys never match in an inner join. Outer joins keep them: a LEFT JOIN returns every left row and fills the right-hand columns with NULLs where nothing matched. That is why WHERE right.id IS NULL is a standard way to find rows with no match.

A subtle consequence: a NULL produced by an outer join and a NULL stored in the data look the same afterwards. If the right side's column can be NULL itself, test the join key (which is never NULL for a real match), not a nullable data column.

Working with NULL

SQL gives a few tools for taking control.

  • x IS NULL and x IS NOT NULL for testing.
  • COALESCE(x, default) returns the first non-NULL argument; use it for display or for calculations where a missing value should count as, say, zero.
  • NULLIF(a, b) returns NULL when a = b, which also helps avoid division by zero: total / NULLIF(count, 0).
  • Arithmetic and string operations with NULL give NULL: 5 + NULL and 'a' || NULL are NULL. (Oracle treats an empty string as NULL, a well-known exception.)

See COALESCE and NULLIF for more patterns.

The NOT IN trap

x IN (1, 2, 3) expands to x = 1 OR x = 2 OR x = 3. x NOT IN (1, 2, 3) is x <> 1 AND x <> 2 AND x <> 3. Now add a NULL to the list: x NOT IN (1, 2, NULL) becomes x <> 1 AND x <> 2 AND x <> NULL. The last part is UNKNOWN, so the whole condition can never be TRUE: either it is FALSE (when x matches) or UNKNOWN. A single NULL in a NOT IN list makes the query return nothing.

SQL NOT IN with a NULL returns no rows; NOT EXISTS does not

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

The first query returns Chen, the second returns an empty result, and both safe versions return Chen again. Prefer NOT EXISTS (see EXISTS) for "has no matching row" questions; it is immune to NULLs and often as fast.

NULL and design

Three-valued logic is a practical argument for declaring NOT NULL wherever the business allows it. Each nullable column forces every query author to think about the third truth value. Where a value really is optional, document what NULL means, and test for it explicitly. Columns with unknown-versus-not-applicable meanings might deserve two designs, such as a separate table that only has rows when the attribute applies.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the result of NULL = NULL in SQL?

    Comparing anything with NULL yields UNKNOWN, even NULL with NULL. Use IS NULL to test for missing values.
  2. 2. Which of these expressions evaluates to TRUE?

    TRUE OR anything is TRUE, since the unknown cannot change the outcome. The other three expressions stay UNKNOWN.
  3. 3. A table has 5 rows; the column points has 2 NULLs and three values summing to 35. What does AVG(points) return?

    AVG ignores NULL inputs, so it divides 35 by the 3 non-NULL values.
  4. 4. SELECT name FROM customer WHERE id NOT IN (SELECT customer_id FROM purchase) returns no rows even though some customers have no purchases. What is the likely cause?

    A NULL in the NOT IN list makes the condition FALSE or UNKNOWN for every row, so nothing is returned. Use NOT EXISTS or filter out NULLs.
  5. 5. Rows where a WHERE condition evaluates to UNKNOWN are...

    WHERE keeps only rows for which the condition is TRUE. FALSE and UNKNOWN rows are both discarded.

Summary

  • NULL marks a missing value (unknown, not applicable or not yet recorded); it is not zero or an empty string.
  • Comparisons with NULL yield UNKNOWN; test with IS NULL / IS NOT NULL.
  • Three-valued logic: TRUE OR UNKNOWN is TRUE, FALSE AND UNKNOWN is FALSE, everything else involving UNKNOWN stays UNKNOWN, and NOT UNKNOWN is UNKNOWN.
  • WHERE keeps only TRUE rows, so rows with NULLs can silently vanish from both a condition and its opposite.
  • Aggregates skip NULLs (except COUNT(*)); DISTINCT and GROUP BY treat NULLs as one group; UNIQUE allows several NULLs in most engines.
  • Inner joins never match NULL keys; NOT IN with a NULL in the list returns nothing, so prefer NOT EXISTS.
  • Use NOT NULL wherever the business allows it.

Next: Enforcing Integrity: Constraints, Assertions and Triggers.