IS NULL / IS NOT NULL — Handle NULLs with IS NULL / IS NOT NULL - SQL

SECTION 1

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.

1970 • Codd's Relational Model

E.F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," introducing the concept of NULL as a marker for missing or inapplicable information in relational tuples.

1979 • Three-Valued Logic Formalized

Codd formally proposes three-valued logic (TRUE, FALSE, UNKNOWN) for SQL predicates involving NULLs, departing from classical Boolean logic used in most programming paradigms.

1986 • SQL-86 Standard

The first ANSI SQL standard (SQL-86) codifies IS NULL and IS NOT NULL as the proper predicates for testing NULL values, explicitly prohibiting the use of = or <> for NULL comparisons.

1992 • SQL-92 Enhancements

SQL-92 introduces COALESCE and NULLIF functions, expanding the toolkit for NULL handling while reinforcing that IS NULL / IS NOT NULL remain the canonical test predicates.

2023 • Modern SQL and NULL Safety

Contemporary databases (PostgreSQL, MySQL 8, SQL Server) implement IS DISTINCT FROM and IS NOT DISTINCT FROM as NULL-safe equality operators, but IS NULL and IS NOT NULL remain the foundational predicates.

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.

SECTION 2

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

NULL represents the absence of data — it is neither zero, nor an empty string, nor FALSE. It is a marker indicating that a value is unknown, missing, or inapplicable for a given column in a given row.

Three-Valued Logic (3VL)

Any comparison involving NULL yields UNKNOWN, not TRUE or FALSE. SQL's WHERE clause only passes rows where the predicate evaluates to TRUE, so UNKNOWN rows are filtered out — just like FALSE.

IS NULL — The Positive Test

The predicate 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

The predicate 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

Arithmetic and string operations with NULL produce NULL: 5 + NULL → NULL, 'hello' || NULL → NULL. NULLs are "contagious" through expressions.

✦

KEY TAKEAWAY

Think of NULL like a sealed, opaque envelope. You cannot compare the contents of a sealed envelope to the number 5, or to the word "hello," or even to another sealed envelope — because you simply don't know what is inside. The only meaningful question you can ask is: "Is this envelope sealed?" That is exactly what IS NULL does — it checks whether the envelope is sealed, without trying to peek inside.

SECTION 3

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.

The flowchart shows two paths when a WHERE predicate involves a NULL column. Using standard comparison operators (=, <>) always yields UNKNOWN, causing the row to be excluded. Using 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.

SECTION 4

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

AND yields TRUE only when both operands are TRUE. FALSE dominates UNKNOWN.

OR Truth Table (3VL)

A	B	A OR B
TRUE	UNKNOWN	TRUE
UNKNOWN	UNKNOWN	UNKNOWN
FALSE	UNKNOWN	UNKNOWN
FALSE	FALSE	FALSE

OR yields FALSE only when both operands are FALSE. TRUE dominates UNKNOWN.

NOT Truth Table (3VL)

A	NOT A
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

NOT UNKNOWN remains UNKNOWN — negation does not resolve uncertainty.

⚠

Critical Implication

Because 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.

SECTION 5

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.

This behavior map shows how NULL propagates through four major SQL contexts: comparisons, aggregates, arithmetic, and sorting. All roads lead back to the same solution — use 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

Summary of NULL behaviors across major SQL contexts

SECTION 6

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	email	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`

Sample customers table — NULL cells highlighted in amber

Finding and Filtering NULL Values

1
Step 1 — Identify customers with no phone number

Write a query that selects customers whose phone column is NULL. The predicate must use IS NULL because WHERE phone = NULL would return zero rows. SELECT name, email FROM customers WHERE phone IS NULL;

Returns Bob (id 2) and Dave (id 4).
2
Step 2 — Identify customers missing any contact information

Use OR to combine two IS NULL checks. This retrieves rows where either email or phone is missing. SELECT name FROM customers WHERE email IS NULL OR phone IS NULL;

Returns Bob (no phone), Carol (no email), and Dave (no email, no phone).
3
Step 3 — Find customers with complete profiles

Use AND to combine two IS NOT NULL checks. Only rows where both fields contain actual values will pass. SELECT name, email, phone FROM customers WHERE email IS NOT NULL AND phone IS NOT NULL;

Returns Alice (id 1) and Eve (id 5) — the only two customers with both email and phone populated.
4
Step 4 — Count completeness using COALESCE

Use COALESCE to substitute a default value when NULL is encountered, useful for reporting. SELECT name, COALESCE(email, 'N/A') AS email, COALESCE(phone, 'N/A') AS phone FROM customers;

All 5 rows returned. NULL values appear as 'N/A' in the output, making reports more readable.
5
Step 5 — Compare COUNT(*) vs COUNT(column)

Demonstrate the difference between counting all rows and counting non-NULL values. SELECT COUNT(*) AS total_rows, COUNT(email) AS has_email, COUNT(phone) AS has_phone FROM customers;

Returns total_rows = 5, has_email = 3 (Alice, Bob, Eve), has_phone = 3 (Alice, Carol, Eve). COUNT(column) silently skips NULLs.

SECTION 7

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

Common NULL pitfalls with their silent failure modes and correct alternatives

🛡️

DEFENSIVE PROGRAMMING PATTERN

When writing any SQL query, mentally check each column referenced in WHERE, JOIN ON, or aggregate functions: "Can this column contain NULL?" If the answer is yes — or even maybe — add explicit NULL handling. This practice is analogous to checking for null pointers in Java or handling Optional in Rust; it is a form of defensive programming that prevents subtle data integrity bugs from reaching production.

SECTION 8

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.

Mapping foundational NULL predicates to their advanced counterparts

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.

SECTION 9

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.

Question 1: CONCEPTUAL

Explain why the following query always returns zero rows, regardless of the data in the table: SELECT * FROM orders WHERE discount = NULL;

Show answer

In SQL's three-valued logic, any comparison using = with NULL evaluates to UNKNOWN, not TRUE or FALSE. The WHERE clause only passes rows where the predicate is TRUE. Since UNKNOWN is not TRUE, every row is filtered out, regardless of whether the discount column actually contains NULL values. The correct predicate is WHERE discount IS NULL, which is specifically designed to test for the NULL marker and returns a proper Boolean TRUE when the column is NULL.

Question 2: BASIC CALCULATION

Given a table employees(id, name, manager_id) where the CEO has a NULL manager_id, write a query to find the CEO's name.

Show answer

The query is: SELECT name FROM employees WHERE manager_id IS NULL; This works because the CEO is the only employee without a manager, so their manager_id is NULL. The IS NULL predicate correctly identifies this row. If you had written WHERE manager_id = NULL, the query would return no rows at all, failing to find the CEO.

Question 3: INTERMEDIATE

A 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.

Show answer

The query is:

SELECT name, price, COALESCE(CAST(weight AS VARCHAR), 'Unknown') AS display_weight FROM products ORDER BY CASE WHEN weight IS NULL THEN 1 ELSE 0 END, weight ASC;

The COALESCE function substitutes 'Unknown' for NULL weight values. The ORDER BY uses a CASE expression to push NULL-weight rows to the end by assigning them a sort key of 1 (versus 0 for non-NULL rows), then sorts by the actual weight ascending within the non-NULL group. Some databases also support the NULLS LAST syntax as an alternative.

Question 4: APPLIED

A data quality report requires you to count, for each column in a 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.

Show answer

The query leverages the fact that COUNT(column) skips NULLs while COUNT(*) counts all rows:

SELECT COUNT(*) - COUNT(q1) AS q1_missing, COUNT(q1) AS q1_answered, COUNT(*) - COUNT(q2) AS q2_missing, COUNT(q2) AS q2_answered, COUNT(*) - COUNT(q3) AS q3_missing, COUNT(q3) AS q3_answered, COUNT(*) - COUNT(q4) AS q4_missing, COUNT(q4) AS q4_answered, COUNT(*) - COUNT(q5) AS q5_missing, COUNT(q5) AS q5_answered FROM survey_responses;

The expression COUNT() - COUNT(qN) gives the number of NULLs in column qN because COUNT() counts every row while COUNT(qN) only counts rows where qN is not NULL. This is a clean, aggregation-based approach that avoids verbose CASE WHEN expressions.

Question 5: CRITICAL THINKING

A colleague writes the following query to find departments that have no employees assigned to a specific project: 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.

Show answer

The bug occurs because the subquery SELECT dept_id FROM project_assignments WHERE project_id = 42 likely returns at least one NULL dept_id (e.g., a project assignment not yet linked to a department). When NOT IN encounters a list containing NULL, it evaluates each comparison as dept_id <> NULL, which is UNKNOWN. Under AND semantics (NOT IN expands to x <> a AND x <> b AND ...), a single UNKNOWN result causes the entire expression to never be TRUE. The fix uses NOT EXISTS, which handles NULLs correctly via semi-join semantics:

SELECT dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM project_assignments pa WHERE pa.project_id = 42 AND pa.dept_id = d.dept_id);

Alternatively, you can filter NULLs from the subquery: WHERE dept_id NOT IN (SELECT dept_id FROM project_assignments WHERE project_id = 42 AND dept_id IS NOT NULL). The NOT EXISTS approach is generally preferred because it is more robust and often better optimized by query planners.

SUMMARY

Lesson Summary

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.