Working with Null Values

The previous three lessons ran into a null value three times: it blanked out a computed expression, left a condition’s negation incomplete, took an engine-dependent place in sorting. Each time, the explanation was deferred. This lesson gives the deferred explanation.

A null value is not a value; it is a marker that states the absence of a value. It is not zero, it is not an empty string; it means “this information does not exist for this row.” The Data Modeling and Relational Theory course defined what a null value means in the schema. Here its behavior in a query gets built, and that behavior comes from a single source: the result of a comparison made with an unknown value is unknown too.

The block below sets up the schema and sample data; every query in this lesson runs against the library.db file created in this block.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

The Result of a Comparison

How a null value behaves in an equality test can be measured directly.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT NULL = NULL AS equal, NULL <> NULL AS different, NULL IS NULL AS is_null;
SQL

equal  different  is_null
-----  ---------  -------
                  1      

The first two columns are blank; the third is 1, meaning true. A null value is neither equal nor unequal to another null value. Whether two unknown numbers are equal to each other cannot be known, because both are unknown. In this case the equality operator produces neither true nor false; it produces a third logical value: unknown.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT 1 = 1 AS true_val, 1 = 2 AS false_val, 1 = NULL AS unknown_val;
SQL

true_val  false_val  unknown_val
--------  ---------  -----------
1         0                     

The three columns show the logic’s three values: true is 1, false is 0, and unknown is blank. The rule that WHERE only lets through true gets tied to a conclusion here: a comparison that includes a null value lets through no row at all — neither in the condition, nor in its negation.

The only correct way to test for a null value is the IS NULL and IS NOT NULL syntax. These are not comparison operators; they are predicates that test for null directly, and they always produce true or false.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, publication_year FROM book WHERE publication_year IS NULL;
SQL

title             publication_year
----------------  ----------------
Motherland Hotel                  

Three-Valued Logic

Logical connectives work with three values too. The rule is to read unknown as “maybe true, maybe false”: the result gets settled if it is the same under both possibilities, and stays unknown if it differs.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT 1 AND NULL AS t_and_u, 0 AND NULL AS f_and_u, 1 OR NULL AS t_or_u,
       0 OR NULL AS f_or_u, NOT NULL AS not_u;
SQL

t_and_u  f_and_u  t_or_u  f_or_u  not_u
-------  -------  ------  ------  -----
         0        1                    

Two columns got settled. False AND unknown is false: whatever the unknown turns out to be, the result is false. True OR unknown is true: one side is already true. The remaining three stayed unknown. Negation does not change unknown — the opposite of unknown is unknown too.

This table is the three-valued counterpart of the short-circuit evaluation rule from the Programming Fundamentals course: when a connective’s result can be settled from one side, the other side does not affect the result.

Substituting a Value for Null

To show a meaningful value in place of null in the result set, the standard function COALESCE is used. It evaluates its arguments left to right and returns the first one that is not null.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT first_name, last_name, COALESCE(email,'(no address)') AS contact FROM member;
SQL

first_name  last_name  contact           
----------  ---------  ------------------
Alice       Kane       [email protected]
Ben         Ortiz      [email protected]  
Clara       Diaz       (no address)      
Derek       Voss       [email protected]
Grace       Kim        (no address)      
Owen        Park       [email protected] 

COALESCE can take more than two arguments; it is used to pick the first filled value from a set of prioritized sources. Many engines also offer a two-argument shorthand, but that shorthand’s name depends on the engine; the portable syntax is COALESCE.

The function that works in the opposite direction, NULLIF, compares two arguments and returns a null value if they are equal, or the first argument if they are not.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT name, NULLIF(city,'Ankara') AS outside_ankara FROM branch ORDER BY branch_id;
SQL

name          outside_ankara
------------  --------------
Central                     
Bahcelievler                
Kadikoy       Istanbul      
Konak         Izmir         

NULLIF is most useful for converting a meaningless placeholder — zero, an empty string, -1 — into a real null value. Guarding against division by zero is a common use too: producing a null value when the divisor is zero is more manageable than getting an error.

Converting a null value can also be written with a conditional expression. The CASE construct is the standard way to turn a column into a readable state.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT loan_id, pickup_date,
       CASE WHEN return_date IS NULL THEN 'checked out' ELSE 'returned' END AS status
FROM loan WHERE loan_id <= 4 ORDER BY loan_id;
SQL

loan_id  pickup_date  status     
-------  -----------  -----------
1        2025-01-10   returned   
2        2025-02-02   returned   
3        2025-02-11   checked out
4        2025-03-01   returned   

Had it been written CASE WHEN return_date = NULL, no row would have been labeled checked out — the condition would never be true, and every row would fall into the ELSE branch. This is the most insidious form of null value mistakes: the query does not raise an error, it gives a wrong answer.

The Negation of a Membership Condition

The most expensive consequence of three-valued logic shows up in the negation of a membership condition. The branch ids where books are held are 1, 2, and 3; the seventh book’s branch has not been set yet, meaning it is null. Let the condition that asks which branch has no books at all first be written without a null value.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT name FROM branch WHERE branch_id NOT IN (1,2,3);
SQL

name 
-----
Konak

The correct answer: the Konak branch has no books at all. Had the same list been produced from the values in the book table, it would have contained a null value too. What happens in that case can be measured.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT name FROM branch WHERE branch_id NOT IN (1,2,3,NULL);
SELECT 'rows returned: ' || COUNT(*) AS measurement
FROM branch WHERE branch_id NOT IN (1,2,3,NULL);
SQL

measurement     
----------------
rows returned: 0

The first query printed no rows; the second confirmed this by counting. A single null value caused the query to return zero rows, and no error was raised. The reason comes directly from three-valued logic: the NOT IN condition expands into “not equal to every item in the list.” In the expression 4 <> 1 AND 4 <> 2 AND 4 <> 3 AND 4 <> NULL, the last factor is unknown; true AND unknown is unknown too. The condition is never true for any row.

When the same list is used in the positive direction, the problem does not show up.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT name FROM branch WHERE branch_id IN (1,2,3,NULL);
SQL

name        
------------
Central     
Bahcelievler
Kadikoy     

Because the IN condition expands with OR, once one side is true, the unknown does not affect the result. The trap is only in the negation, and this asymmetry is the main reason the mistake goes unnoticed: because the positive query works correctly, its negation is assumed to work correctly too.

The way to guard against it is to eliminate null values at the source that produces the list, or to write the condition with a null-safe predicate. The standard has the IS DISTINCT FROM predicate for this; it treats null values as comparable too.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT 1 IS DISTINCT FROM NULL AS one_and_null,
       NULL IS DISTINCT FROM NULL AS null_and_null,
       NULL IS NOT DISTINCT FROM NULL AS null_equals_null;
SQL

one_and_null  null_and_null  null_equals_null
------------  -------------  ----------------
1             0              1               

All three columns produced true or false; none of them are unknown. Support for the IS DISTINCT FROM predicate depends on the engine; on engines where it is missing, the same effect is achieved by explicitly adding a null test to the condition.

How null values behave in aggregate functions follows a separate rule too, and it will be measured in the second topic: aggregate functions do not count null values.

Summary

• A null value is a marker that states the absence of a value; it is not zero or an empty string.
• A comparison made with a null value produces neither true nor false, but unknown; because WHERE only lets through true, this eliminates the row in both the condition and its negation.
• Null is tested only with IS NULL and IS NOT NULL.
• COALESCE returns the first argument that is not null, NULLIF returns a null value on equality; inside CASE, a null test is written with IS NULL, not with equality.
• A single null value in a NOT IN list makes the query return zero rows without an error; the same list used with IN does not show the problem.
• IS DISTINCT FROM gives a null-safe comparison; support for it depends on the engine.

Next Step

In this lesson, COALESCE, NULLIF, and CASE were used in the context of null values; yet all three are members of a wider family. A query does not have to show column values as they are — it can cut text, round a number, extract a year from a date. The next lesson takes up string, number, and date functions and shows why the naming of date functions is the area where the standard is applied the least.