Historical Context & Motivation
The concept of NULL in relational databases traces back to the foundational work of E.F. Codd, who recognized that real-world data is frequently incomplete, unknown, or inapplicable. Unlike programming languages that typically use sentinel values such as zero, empty strings, or dedicated null pointers, Codd envisioned a special marker within the relational model itself to represent the absence of a value rather than a value of zero or an empty string. This distinction carries profound implications for how comparisons, aggregations, and logical expressions behave in SQL, and it necessitated entirely new operators — IS NULL and IS NOT NULL — to test for this special state.
The central question this lesson addresses is deceptively simple: if NULL is not a value, how do you test for it? Why does WHERE column = NULL always return zero rows, and what must you write instead? Understanding the answer requires grappling with three-valued logic — the subtle but critical departure from the Boolean logic you encounter in general-purpose programming languages.
Core Principles & Definitions
Before writing queries that handle NULLs correctly, you need to internalize several foundational principles that govern how SQL engines treat this special marker. These principles explain why NULL behaves differently from every other token in the language and why dedicated predicates are required.
NULL Is Not a Value
Three-Valued Logic (3VL)
IS NULL — The Positive Test
column IS NULL evaluates to TRUE when the column contains NULL, and FALSE otherwise. It is the only correct way to check for the presence of NULL.IS NOT NULL — The Negative Test
column IS NOT NULL evaluates to TRUE when the column contains any actual value (including zero, empty string, or spaces), and FALSE when it is NULL.NULL Propagation
5 + NULL → NULL, 'hello' || NULL → NULL. NULLs are "contagious" through expressions.IS NULL does — it checks whether the envelope is sealed, without trying to peek inside.Visual Explanation — Three-Valued Logic Flow
The diagram below illustrates how SQL's WHERE clause evaluates predicates under three-valued logic. When a column value is compared using standard operators (=, <>, <, >, etc.), the result can be TRUE, FALSE, or UNKNOWN. Only rows that evaluate to TRUE pass the filter. This is precisely why WHERE col = NULL silently drops every row — the comparison always yields UNKNOWN, which the WHERE clause treats identically to FALSE.
IS NULL or IS NOT NULL correctly produces TRUE or FALSE, allowing proper filtering.Notice that the diagram's right branch — the one representing col = NULL — leads directly to row exclusion with no opportunity for the row to pass. This is the single most common bug in SQL queries written by newcomers to the language. The left branch, using IS NULL or IS NOT NULL, properly evaluates to a definite Boolean result, allowing the WHERE clause to include or exclude the row as intended.
How Three-Valued Logic Works
SQL implements Kleene's three-valued logic (3VL), which extends classical Boolean logic by adding a third truth value: UNKNOWN. Every predicate in SQL evaluates to one of three states: TRUE (T), FALSE (F), or UNKNOWN (U). The behavior of the logical connectives AND, OR, and NOT under 3VL determines how compound predicates involving NULLs behave. Understanding these truth tables is essential for writing correct WHERE clauses that combine NULL-aware predicates with other conditions.
AND Truth Table (3VL)
| A | B | A AND B |
|---|---|---|
| TRUE | TRUE | TRUE |
| TRUE | UNKNOWN | UNKNOWN |
| TRUE | FALSE | FALSE |
| UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | FALSE |
| FALSE | FALSE | FALSE |
OR Truth Table (3VL)
| A | B | A OR B |
|---|---|---|
| TRUE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | UNKNOWN |
| FALSE | FALSE | FALSE |
NOT Truth Table (3VL)
| A | NOT A |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
NOT (col = NULL) evaluates as NOT UNKNOWN which is still UNKNOWN, writing WHERE NOT (col = NULL) also returns zero rows. You cannot work around the problem by wrapping the comparison in NOT — you must use IS NOT NULL.NULL Behavior Across SQL Contexts
NULLs do not only affect WHERE clauses — their behavior permeates virtually every SQL operation. The following diagram and table catalog these behaviors systematically, helping you anticipate where NULLs can produce surprising results.
IS NULL and IS NOT NULL for explicit NULL detection.| SQL Context | NULL Behavior | Implication |
|---|---|---|
| WHERE / HAVING | UNKNOWN treated as FALSE | Rows with NULL in filtered column silently disappear unless IS NULL / IS NOT NULL is used |
| GROUP BY | All NULLs grouped together | Despite NULL ≠ NULL in comparisons, GROUP BY treats all NULLs as one group |
| DISTINCT | NULLs considered duplicates | SELECT DISTINCT collapses multiple NULL rows into one NULL |
| COUNT(col) | Skips NULLs | COUNT(*) counts all rows; COUNT(col) counts only non-NULL values — a common source of off-by-one-style errors |
| UNIQUE constraint | Multiple NULLs allowed (most vendors) | Since NULL ≠ NULL, multiple NULLs do not violate uniqueness in PostgreSQL, MySQL, and Oracle (but SQL Server differs) |
| IN / NOT IN | NOT IN with NULLs returns empty set | If the subquery or list contains any NULL, NOT IN yields UNKNOWN for every row — a notorious pitfall |
Worked Example — Filtering Incomplete Customer Records
Consider a customers table with columns id, name, email, and phone. Some customers registered without providing a phone number or email. The task is to find customers who are missing contact information and then to identify customers who have complete profiles.
| id | name | phone | |
|---|---|---|---|
| 1 | Alice | alice@ex.com | 555-0101 |
| 2 | Bob | bob@ex.com | NULL |
| 3 | Carol | NULL | 555-0303 |
| 4 | Dave | NULL | NULL |
| 5 | Eve | eve@ex.com | 555-0505 |
IS NULL because WHERE phone = NULL would return zero rows.
SELECT name, email FROM customers WHERE phone IS NULL;SELECT name FROM customers WHERE email IS NULL OR phone IS NULL;SELECT name, email, phone FROM customers WHERE email IS NOT NULL AND phone IS NOT NULL;SELECT name, COALESCE(email, 'N/A') AS email, COALESCE(phone, 'N/A') AS phone FROM customers;SELECT COUNT(*) AS total_rows, COUNT(email) AS has_email, COUNT(phone) AS has_phone FROM customers;Common Pitfalls and Defensive Patterns
NULL-related bugs are among the most insidious in SQL because they tend to produce silently wrong results rather than errors. A query that uses WHERE col = NULL will execute without any syntax error — it simply returns an empty result set, leading developers to believe the table has no matching data when in fact the predicate is logically malformed. The table below catalogs the most common pitfalls and their correct alternatives.
| Pitfall | What Happens | Correct Pattern |
|---|---|---|
WHERE col = NULL | Always yields UNKNOWN; returns 0 rows | WHERE col IS NULL |
WHERE col <> NULL | Always yields UNKNOWN; returns 0 rows | WHERE col IS NOT NULL |
WHERE col NOT IN (SELECT ...) | Returns empty set if subquery has any NULL | WHERE col NOT IN (SELECT ... WHERE x IS NOT NULL) |
WHERE col = '' | Finds empty strings, not NULLs — they are different | WHERE col IS NULL OR col = '' |
| Using AVG without awareness | AVG skips NULLs — denominator only includes non-NULL rows | AVG(COALESCE(col, 0)) if zeros are appropriate |
| Joining on nullable columns | NULL = NULL is UNKNOWN; rows with NULL keys never match | Use COALESCE on join keys or IS NOT DISTINCT FROM |
Connection to Advanced NULL Handling
While IS NULL and IS NOT NULL are the foundational predicates for NULL detection, the SQL standard and modern database engines offer more sophisticated tools for NULL handling. Understanding these advanced constructs positions you to write more concise, more robust, and more portable queries. The table below maps each foundational concept to its advanced counterpart.
| Foundational Concept | Advanced Extension | Description |
|---|---|---|
IS NULL | COALESCE(a, b, ...) | Returns the first non-NULL argument. Replaces common CASE WHEN ... IS NULL patterns with a concise function call. |
IS NOT NULL | NULLIF(a, b) | Returns NULL if a equals b, otherwise returns a. Useful for converting sentinel values (e.g., 0 or '') back to NULL for correct aggregation. |
| NULL = NULL → UNKNOWN | IS [NOT] DISTINCT FROM | NULL-safe equality operator (SQL:2003). NULL IS NOT DISTINCT FROM NULL → TRUE. Eliminates the need for verbose IS NULL OR a = b patterns in JOIN conditions. |
CASE WHEN x IS NULL | IFNULL(a, b) | Vendor-specific shorthand (MySQL, SQLite) equivalent to COALESCE with two arguments. |
| NOT IN pitfall | NOT EXISTS | EXISTS uses semi-join semantics that handle NULLs correctly. Always prefer NOT EXISTS over NOT IN when the subquery column is nullable. |
As you advance to topics such as window functions, common table expressions (CTEs), and recursive queries, NULL handling becomes even more critical. Window functions like LAG and LEAD produce NULLs at partition boundaries, OUTER JOINs introduce NULLs for non-matching rows, and recursive CTEs must guard against NULL propagation to avoid infinite loops or silent data loss. The skills you build with IS NULL and IS NOT NULL form the foundation for all of these advanced patterns.
Practice Problems
The following five problems escalate in difficulty, from conceptual understanding to critical analysis. Work through each one, writing out the SQL before checking the answer. Assume standard ANSI SQL behavior unless otherwise specified.
SELECT * FROM orders WHERE discount = NULL;employees(id, name, manager_id) where the CEO has a NULL manager_id, write a query to find the CEO's name.products table has columns (id, name, price, weight). Some products have NULL weight. Write a single query that returns all products, showing the weight where available and 'Unknown' where it is NULL, and sort so that products with known weights appear first (ascending), followed by unknowns.survey_responses(id, q1, q2, q3, q4, q5) table, how many responses are NULL (unanswered) versus non-NULL. Write a query that produces a single result row with columns q1_missing, q1_answered, q2_missing, q2_answered, ... and so on for all five questions.SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM project_assignments WHERE project_id = 42);
The query returns zero rows even though you know some departments have no one on project 42. Diagnose the bug and provide a corrected version. Explain why your fix works.Lesson Summary
NULL is a special marker in SQL representing the absence of a value — it is not zero, not an empty string, and not FALSE. Because SQL uses three-valued logic (TRUE, FALSE, UNKNOWN), any comparison with NULL using standard operators (=, <>, <, >) evaluates to UNKNOWN, which the WHERE clause treats identically to FALSE. This is why IS NULL and IS NOT NULL exist: they are the only predicates that can properly evaluate to TRUE or FALSE when testing for the NULL marker.
Key behaviors to remember: NULL propagates through arithmetic (5 + NULL → NULL), aggregate functions like COUNT(col) skip NULLs while COUNT(*) includes all rows, and NOT IN with NULLs in the subquery returns an empty set. For advanced NULL handling, use COALESCE to supply default values, NULLIF to convert sentinel values back to NULL, and NOT EXISTS instead of NOT IN when nullable columns are involved.