NULL in SQL & NOT IN Traps
Understand three-valued logic in SQL. Learn why NOT IN returns zero rows with NULLs, trace SUM and COUNT null handling, and write safe COALESCE queries.
See the About page for curation and verification details.
In relational databases, NULL represents the total absence of a value or an unknown state, not a zero or an empty string. The introduction of NULL creates Three-Valued Logic (3VL), where boolean expressions evaluate to TRUE, FALSE, or UNKNOWN. In production data pipelines, misunderstanding how NULL propagates through boolean operators, subqueries, and aggregate functions is one of the most common sources of silent data bugs. This guide examines Three-Valued Logic, explains the dangerous NOT IN subquery trap with verified SQLite code, and provides clear rules for aggregate function behavior.
Three-Valued Logic (3VL) Explained
Standard programming languages operate on two-valued boolean logic: a statement is either true or false. Relational databases, following Edgar F. Codd's relational model, introduce UNKNOWN to handle missing information. The truth tables for Three-Valued Logic lead to counterintuitive outcomes:
| Expression | Evaluation Result | Reason / Explanation |
|---|---|---|
| NULL = NULL | UNKNOWN | Two unknown values cannot be confirmed as equal |
| NULL != NULL | UNKNOWN | Two unknown values cannot be confirmed as distinct |
| NULL IS NULL | TRUE | Special unary predicate testing absence of value |
| TRUE AND UNKNOWN | UNKNOWN | Result depends on whether the unknown value is true or false |
| FALSE AND UNKNOWN | FALSE | False dominates in AND logic regardless of unknown value |
| TRUE OR UNKNOWN | TRUE | True dominates in OR logic regardless of unknown value |
| FALSE OR UNKNOWN | UNKNOWN | Result depends on whether the unknown value is true or false |
| NOT UNKNOWN | UNKNOWN | Negating an unknown condition remains unknown |
The critical database rule to memorize is: WHERE clauses discard rows unless the predicate evaluates strictly to TRUE. If a WHERE condition evaluates to UNKNOWN or FALSE, the row is filtered out.
The Dangerous NOT IN Subquery Trap
Consider an e-commerce database with two tables: customers and blacklisted_users. We want to select all customers who are not blacklisted. Let us test what happens in SQLite 3.49 when blacklisted_users contains a single NULL value:
CREATE TABLE customers (id INT, name TEXT, phone TEXT);
INSERT INTO customers VALUES (1, 'Alice', '555-0100'), (2, 'Bob', NULL), (3, 'Charlie', '555-0199');
CREATE TABLE blacklisted_users (user_id INT);
INSERT INTO blacklisted_users VALUES (2), (NULL);
-- The Dangerous Query:
SELECT id, name FROM customers WHERE id NOT IN (SELECT user_id FROM blacklisted_users);Verified SQLite output: (0 rows returned)
The query returns zero rows! Even though Alice (id 1) and Charlie (id 3) are not in the blacklisted table, the query returns completely empty results. Let us trace why:
The expression id NOT IN (2, NULL) is mathematically expanded by the SQL parser as:
id != 2 AND id != NULL.
For Alice (id = 1):
- 1 != 2 evaluates to TRUE.
- 1 != NULL evaluates to UNKNOWN.
- TRUE AND UNKNOWN evaluates to UNKNOWN.
Because UNKNOWN is not TRUE, Alice is filtered out! The same logic applies to every row in the table. If a subquery returns even a single NULL value, NOT IN evaluates to UNKNOWN for all non-matching rows, wiping out the entire result set.
The Safe Production Solution: NOT EXISTS
The professional solution is using the NOT EXISTS correlated subquery or a LEFT JOIN ... WHERE NULL anti-join pattern. NOT EXISTS tests for row existence rather than value equality, making it completely immune to NULL values inside subqueries:
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM blacklisted_users b WHERE b.user_id = c.id
);Verified SQLite output: 1 | Alice 3 | Charlie
NOT EXISTS correctly preserves Alice and Charlie! If you must use IN, you must explicitly sanitize the subquery with WHERE user_id IS NOT NULL.
How Aggregate Functions Handle NULLs
Another major area of confusion is how SQL aggregate functions handle NULL values. Consider a table of employee bonus points:
CREATE TABLE performance (dept TEXT, score INT);
INSERT INTO performance VALUES ('Sales', 10), ('Sales', 20), ('Sales', NULL);
SELECT
COUNT(*) AS total_rows,
COUNT(score) AS scored_rows,
SUM(score) AS total_score,
AVG(score) AS avg_score
FROM performance;Verified SQLite output: 3 | 2 | 30 | 15.0
Notice the crucial distinctions:
- COUNT(*) counts all physical rows in the partition (returns 3).
- COUNT(score) counts only non-NULL values (returns 2).
- SUM(score) ignores NULLs and computes 10 + 20 = 30.
- AVG(score) divides the sum by COUNT(score), NOT COUNT(*). The calculation is 30 / 2 = 15.0, NOT 30 / 3 = 10.0!
If your business requirement specifies that missing scores should count as zeros, you must explicitly use COALESCE: AVG(COALESCE(score, 0)) produces 10.0.
Defensive NULL Handling: COALESCE and NULLIF
Production SQL requires defensive patterns to prevent runtime calculation exceptions and unexpected data filtering:
- COALESCE(expression, fallback): Evaluates arguments sequentially and returns the first non-NULL value. Used extensively in outer joins to provide default display text: COALESCE(d.department_name, 'Unassigned').
- NULLIF(a, b): Returns NULL if a == b, otherwise returns a. This pattern is the primary safeguard against runtime division-by-zero errors in analytical calculations: SELECT numerator / NULLIF(denominator, 0) FROM metrics;. If denominator is 0, NULLIF evaluates to NULL, and dividing by NULL safely produces NULL rather than crashing the query pipeline with ZeroDivisionError.
- IS DISTINCT FROM: Standard ANSI SQL predicate testing whether two expressions are distinct, treating two NULLs as equivalent. While NULL = NULL evaluates to UNKNOWN, NULL IS DISTINCT FROM NULL evaluates cleanly to FALSE.
NULLs in Relational Set Operations: UNION, INTERSECT, and EXCEPT
A fascinating paradox in SQL is that while NULL = NULL evaluates to UNKNOWN in WHERE and JOIN clauses, relational set operations (UNION, INTERSECT, and EXCEPT) treat NULLs as identical for deduplication:
- In UNION: Multiple rows containing NULL are collapsed into a single NULL row. The engine groups duplicate NULLs together.
- In INTERSECT: If both input sets contain a NULL row, INTERSECT outputs NULL, recognizing that both queries produced matching unknown values.
- In EXCEPT: If both queries contain NULL, EXCEPT removes NULL from the final output.
This distinction exists because set operations operate under mathematical set equivalence (where two missing entries represent identical set memberships) rather than scalar boolean truth evaluations.
Outer Joins & Anti-Join Mechanics with NULLs
The most powerful practical application of NULL in SQL query architecture is the Anti-Join pattern. When performing a LEFT JOIN, any row from the left table that has no matching relation on the right table has all right-table columns populated with NULL.
By filtering WHERE right_table.primary_key IS NULL, we cleanly isolate records that exist exclusively on the left side. This anti-join pattern is functionally equivalent to NOT EXISTS and significantly faster than NOT IN on large unindexed foreign key tables.
Four Common NULL Traps to Avoid in SQL
- Using
WHERE col = NULLorWHERE col != NULL: This always evaluates to UNKNOWN and filters out all rows. You must useWHERE col IS NULLorWHERE col IS NOT NULL. - Forgetting that
NOT INfails on NULLs: Always preferNOT EXISTSoverNOT INwhen subquery columns can contain NULLs. - Manual average calculations: Writing
SUM(val) / COUNT(*)whenvalcontains NULLs produces a different result thanAVG(val). - Ordering NULLs inconsistently: In SQLite, PostgreSQL, and MySQL, the default placement of NULLs in
ORDER BYvaries. Use explicitNULLS FIRSTorNULLS LASTto ensure deterministic sorting.
NULL Practice Questions & Walkthroughs
Question 1:
Predict the output of this query:
SELECT CASE WHEN NULL = NULL THEN 'Equal' ELSE 'Not Equal' END AS result;Walkthrough: The condition NULL = NULL evaluates to UNKNOWN. In a CASE statement, branches execute only when conditions evaluate strictly to TRUE. Because UNKNOWN is not TRUE, control falls through to the ELSE branch. Output: 'Not Equal'.
Question 2:
Write a query finding customers whose phone number is not '555-0100', including customers who have no phone number on file.
-- Mistaken attempt: WHERE phone != '555-0100' (filters out NULLs!)
-- Correct Solution:
SELECT name, phone
FROM customers
WHERE phone != '555-0100' OR phone IS NULL;Walkthrough: In SQL, NULL != '555-0100' evaluates to UNKNOWN. To retain records with missing phone numbers, you must explicitly add OR phone IS NULL.
Question 3:
How does COALESCE differ from IFNULL?
Solution: COALESCE is ANSI SQL standard and accepts an arbitrary number of arguments (COALESCE(a, b, c, 'default')), returning the first non-NULL operand. IFNULL is an older two-argument function supported by specific engines like SQLite and MySQL.
Question 4:
How does COUNT(DISTINCT column) evaluate rows containing NULLs?
Solution: Standard SQL aggregate behavior dictates that COUNT(DISTINCT column) completely discards NULLs before computing distinct cardinality. If a column contains [10, 20, 20, NULL, NULL], COUNT(DISTINCT column) returns 2 (counting 10 and 20), ignoring all NULL values.
Question 5:
What is the behavior of NULL in ORDER BY with NULLS FIRST versus NULLS LAST?
Solution: The SQL:2003 standard allows explicit ordering syntax: ORDER BY salary DESC NULLS LAST. In SQLite, NULLs sort as smaller than any other value, meaning ascending orders put NULLs first and descending orders put NULLs last. When writing portable SQL, always specify NULLS FIRST or NULLS LAST explicitly to prevent database-specific sort variations.
Practice handling real data edge cases in our SQL Basic Querying Practice workspace and read our related guide on Visualizing SQL Joins.