Database Management SystemsNULL Handling

NULL Handling in SQL

LevelBeginner

Duration50 mins

TopicNULL Handling

2 / 5

IS NULL: Detecting Missing Values

The One True Way to Check for NULL

In the previous page, we established that NULL is not a value—it's a marker indicating missing information. We also learned that comparing NULL using standard operators like = doesn't work as expected.

So how do we actually detect NULL values in our data?

SQL provides a special predicate specifically designed for this purpose: IS NULL. This is not merely a convenience—it's the only correct way to test for NULL in SQL. Any other approach either fails silently or produces incorrect results.

This page explores IS NULL in exhaustive detail: its syntax, behavior in different contexts, common patterns, and the subtle distinctions that separate correct NULL handling from broken queries.

What You Will Learn

By the end of this page, you will master the IS NULL predicate—understanding its syntax, behavior with compound conditions, use in subqueries and JOINs, performance implications, and common patterns for finding missing data in production databases.

IS NULL Fundamentals

The IS NULL predicate is a special syntactic construct that tests whether an expression evaluates to NULL. Unlike regular comparison operators, IS NULL returns a definite TRUE or FALSE—never UNKNOWN.

Syntax:

expression IS NULL

Behavior:

If expression evaluates to NULL → Returns TRUE
If expression evaluates to any non-NULL value → Returns FALSE

This binary behavior is crucial: IS NULL gives you a clear, predictable answer that works correctly in WHERE clauses.

is_null_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Basic IS NULL examples
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),       -- Nullable
    phone VARCHAR(20),             -- Nullable
    manager_id INT,                -- Nullable (CEO has no manager)
    commission_rate DECIMAL(5,2)   -- Nullable (non-sales have no commission)
);
 
-- Find employees without a middle name
SELECT employee_id, first_name, last_name
FROM employees
WHERE middle_name IS NULL;
 
-- Find employees without a phone number on record
SELECT employee_id, first_name, last_name
FROM employees
WHERE phone IS NULL;
 
-- Find employees without a manager (typically CEO or top-level)
SELECT employee_id, first_name, last_name
FROM employees
WHERE manager_id IS NULL;
 
-- Find employees who are NOT on commission
-- (sales staff would have a commission_rate value)
SELECT employee_id, first_name, last_name
FROM employees
WHERE commission_rate IS NULL;
 
-- Combine with other conditions
-- Find employees hired after 2020 who don't have phones
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2020-01-01'
  AND phone IS NULL;

Always Prefer IS NULL Over Alternatives

Some databases support non-standard NULL comparisons (like MySQL's NULL-safe equality <=>). While these exist, IS NULL is the ANSI SQL standard, universally supported, and immediately recognized by any SQL developer. Use IS NULL for portability and clarity.

IS NULL vs = NULL: The Critical Difference

Let's examine exactly why = NULL fails and IS NULL succeeds. Understanding this difference at a deep level prevents subtle bugs.

Using = NULL (WRONG)

•Treats NULL as a value to compare against other values
•Returns UNKNOWN when either side is NULL
•WHERE clause excludes UNKNOWN rows as if they were FALSE
•Silent failure: No error, just zero results
•Hard to debug: Query looks syntactically correct

Using IS NULL (CORRECT)

•Special predicate designed specifically for NULL testing
•Returns TRUE or FALSE definitively—never UNKNOWN
•WHERE clause includes TRUE rows correctly
•Correct behavior: Returns rows where column is NULL
•Self-documenting: Clear intent to check for NULL

is_null_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Sample data
CREATE TABLE test_null (
    id INT PRIMARY KEY,
    value INT
);
 
INSERT INTO test_null VALUES 
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 100);
 
-- WRONG: This returns ZERO rows
SELECT * FROM test_null WHERE value = NULL;
-- For row id=2: NULL = NULL → UNKNOWN → excluded
-- For row id=4: NULL = NULL → UNKNOWN → excluded
-- For row id=1: 100 = NULL → UNKNOWN → excluded
-- ALL rows are excluded because ALL comparisons yield UNKNOWN!
 
-- CORRECT: This returns rows 2 and 4
SELECT * FROM test_null WHERE value IS NULL;
-- For row id=2: NULL IS NULL → TRUE → included
-- For row id=4: NULL IS NULL → TRUE → included
-- For row id=1, 3, 5: value IS NULL → FALSE → excluded
 
-- Another common mistake:
-- Trying to check if two columns both contain NULL
CREATE TABLE pairs (
    id INT,
    col_a INT,
    col_b INT
);
 
INSERT INTO pairs VALUES 
(1, NULL, NULL),
(2, NULL, 10),
(3, 10, NULL),
(4, 10, 10);
 
-- WRONG: Returns zero rows (both NULLs don't match with =)
SELECT * FROM pairs WHERE col_a = col_b;
 
-- To find rows where both columns are NULL:
SELECT * FROM pairs 
WHERE col_a IS NULL AND col_b IS NULL;
 
-- To find rows where columns have the same value OR both are NULL:
SELECT * FROM pairs 
WHERE col_a = col_b 
   OR (col_a IS NULL AND col_b IS NULL);

Why Doesn't SQL Just Make = NULL Work?

You might wonder why SQL doesn't just make = NULL return TRUE when comparing NULL to NULL. The reason is consistency: if NULL = NULL were TRUE, then NULL <> NULL would have to be FALSE. But we don't know if two unknown values are different either! SQL's designers chose consistency over convenience. IS NULL exists specifically to handle this case.

IS NULL in Complex Expressions

IS NULL can be applied to any expression, not just simple column references. This includes arithmetic expressions, function results, subqueries, and CASE expressions.

is_null_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- IS NULL with arithmetic expressions
-- NULL propagates through arithmetic, so we can detect it
SELECT product_id, price, discount
FROM products
WHERE (price - discount) IS NULL;
-- Returns products where either price OR discount is NULL
-- (since NULL - anything = NULL and anything - NULL = NULL)
 
-- IS NULL with function results
SELECT customer_id, first_name, last_name
FROM customers
WHERE TRIM(middle_name) IS NULL;
-- TRIM(NULL) returns NULL
 
-- IS NULL with concatenation
-- In most databases: NULL || 'string' = NULL
SELECT employee_id, first_name, middle_name, last_name
FROM employees  
WHERE (first_name || ' ' || middle_name || ' ' || last_name) IS NULL;
-- This would catch any NULL in any of the name columns
 
-- IS NULL with CASE expressions
SELECT 
    product_id,
    product_name,
    CASE 
        WHEN stock_quantity > 100 THEN 'High'
        WHEN stock_quantity > 10 THEN 'Medium'
        WHEN stock_quantity > 0 THEN 'Low'
        -- No ELSE clause means NULL when stock_quantity is 0 or NULL
    END AS stock_level
FROM products
WHERE (
    CASE 
        WHEN stock_quantity > 100 THEN 'High'
        WHEN stock_quantity > 10 THEN 'Medium'
        WHEN stock_quantity > 0 THEN 'Low'
    END
) IS NULL;
 
-- IS NULL with scalar subqueries
SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
WHERE (
    SELECT d.department_name 
    FROM departments d 
    WHERE d.department_id = e.department_id
) IS NULL;
-- Finds employees whose department_id doesn't match any department
-- (subquery returns NULL, which IS NULL detects)

Expression IS NULL vs Column IS NULL

When you use expression IS NULL, you're testing whether the entire expression evaluates to NULL. This is powerful for detecting NULL propagation through calculations. If (price * quantity) IS NULL, you know that either price or quantity (or both) is NULL.

IS NULL with Aggregate Functions:

Aggregate functions have special NULL handling, but their results can still be tested with IS NULL:

is_null_aggregates.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- When does an aggregate return NULL?
 
-- SUM, AVG, MIN, MAX return NULL when applied to empty set
SELECT SUM(amount) FROM orders WHERE 1 = 0;  -- Returns NULL
SELECT AVG(amount) FROM orders WHERE 1 = 0;  -- Returns NULL
 
-- COUNT never returns NULL (returns 0 for empty set)
SELECT COUNT(*) FROM orders WHERE 1 = 0;     -- Returns 0
SELECT COUNT(amount) FROM orders WHERE 1 = 0; -- Returns 0
 
-- Using IS NULL with HAVING to find groups with no data
SELECT 
    department_id,
    SUM(bonus) as total_bonus
FROM employees
GROUP BY department_id
HAVING SUM(bonus) IS NULL;
-- Finds departments where ALL employees have NULL bonus
-- (SUM of all NULLs = NULL)
 
-- Practical example: Find product categories with no sales
SELECT 
    c.category_id,
    c.category_name,
    SUM(oi.quantity * oi.unit_price) as total_revenue
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_id, c.category_name
HAVING SUM(oi.quantity * oi.unit_price) IS NULL;

IS NULL with JOIN Operations

IS NULL becomes particularly powerful when combined with outer joins. It allows you to find rows that don't match across tables—a common and important query pattern.

is_null_joins.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Classic anti-join pattern: Find customers with no orders
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
 
-- Why this works:
-- LEFT JOIN keeps all customers, even without matching orders
-- For customers with no orders, all order columns are NULL
-- WHERE o.order_id IS NULL filters to just those unmatched rows
 
-- Find products never ordered
SELECT p.product_id, p.product_name, p.unit_price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;
 
-- Find employees with no direct reports
SELECT m.employee_id, m.first_name, m.last_name
FROM employees m
LEFT JOIN employees r ON m.employee_id = r.manager_id
WHERE r.employee_id IS NULL;
-- Uses self-join: m is potential managers, r is potential reports
 
-- Find departments with no employees
SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
 
-- Multi-table anti-join: Find categories with no products that have orders
SELECT c.category_id, c.category_name
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

Anti-Join vs NOT EXISTS vs NOT IN

The LEFT JOIN + IS NULL pattern is called an 'anti-join'. It achieves the same result as NOT EXISTS and NOT IN (when NULL-safe). The anti-join pattern is often preferred because it's visually clear and typically performs well. We'll compare these patterns later when discussing COALESCE alternatives.

FULL OUTER JOIN with IS NULL:

FULL OUTER JOIN combined with IS NULL can identify unmatched rows on both sides of a join—useful for data reconciliation and finding orphaned records.

full_outer_join_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Find mismatches between two systems' customer records
-- (e.g., CRM vs Order System)
SELECT 
    crm.customer_id AS crm_id,
    crm.customer_name AS crm_name,
    orders.customer_id AS order_id,
    orders.customer_name AS order_name,
    CASE 
        WHEN crm.customer_id IS NULL 
            THEN 'Only in Order System'
        WHEN orders.customer_id IS NULL 
            THEN 'Only in CRM'
        ELSE 'In Both'
    END AS status
FROM crm_customers crm
FULL OUTER JOIN order_system_customers orders 
    ON crm.customer_id = orders.customer_id
WHERE crm.customer_id IS NULL 
   OR orders.customer_id IS NULL;
 
-- This query identifies:
-- 1. Customers in CRM but not Order System (orders.customer_id IS NULL)
-- 2. Customers in Order System but not CRM (crm.customer_id IS NULL)
 
-- Audit pattern: Compare staging table to production
SELECT 
    COALESCE(s.record_id, p.record_id) AS record_id,
    s.value AS staging_value,
    p.value AS production_value,
    CASE 
        WHEN s.record_id IS NULL THEN 'DELETE (in prod, not staging)'
        WHEN p.record_id IS NULL THEN 'INSERT (in staging, not prod)'
        WHEN s.value <> p.value THEN 'UPDATE (values differ)'
        ELSE 'UNCHANGED'
    END AS action_needed
FROM staging_table s
FULL OUTER JOIN production_table p ON s.record_id = p.record_id
WHERE s.record_id IS NULL 
   OR p.record_id IS NULL
   OR s.value <> p.value;

IS NULL Performance Considerations

IS NULL queries have specific performance characteristics that database developers should understand. Index usage, query planning, and NULL density all affect how quickly these queries execute.

IS NULL Performance Factors

•Index Usage — Most databases can use indexes for IS NULL conditions, but behavior varies. B-tree indexes typically include NULL values; the query planner decides whether an index scan is worthwhile.
•NULL Density — If 99% of rows have NULL in a column, an index on that column may not help IS NULL queries (the optimizer may choose a table scan instead). Conversely, if only 1% are NULL, an index can dramatically speed up IS NULL.
•Leading Column Position — For multi-column indexes, IS NULL works best when the NULL-checked column is the leading (leftmost) column. A trailing column IS NULL check may not use the index effectively.
•Index-Only Scans — If your query only needs columns that are all in the index, the database can answer IS NULL without touching the main table—a significant performance win.
•Partial Indexes — Some databases (PostgreSQL, SQLite) support partial indexes that include only rows where a condition is true. You can create an index on rows where a column IS NOT NULL, which is smaller and faster.

is_null_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Example: Analyzing IS NULL performance
 
-- Create a table with varying NULL density
CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20),
    processed_date TIMESTAMP,  -- NULL for unprocessed records
    amount DECIMAL(10,2)
);
 
-- Index on the nullable column
CREATE INDEX idx_processed_date ON large_table(processed_date);
 
-- This query may or may not use the index depending on NULL density
EXPLAIN ANALYZE
SELECT * FROM large_table WHERE processed_date IS NULL;
 
-- PostgreSQL: Create a partial index for unprocessed records
-- This is smaller and faster than a full index
CREATE INDEX idx_unprocessed ON large_table(id) 
WHERE processed_date IS NULL;
 
-- Now this query efficiently uses the partial index
SELECT * FROM large_table WHERE processed_date IS NULL;
 
-- Covering index for IS NULL query (index-only scan)
CREATE INDEX idx_covering ON large_table(processed_date, id, status);
 
-- This query can be answered entirely from the index
SELECT id, status FROM large_table WHERE processed_date IS NULL;
 
-- Composite index with NULL column
CREATE INDEX idx_status_processed 
    ON large_table(status, processed_date);
 
-- This uses the index (status is leading, equals condition)
SELECT * FROM large_table 
WHERE status = 'pending' AND processed_date IS NULL;
 
-- This may not use the index well (status not constrained)
SELECT * FROM large_table WHERE processed_date IS NULL;

Check Your Execution Plan

Always use EXPLAIN or EXPLAIN ANALYZE to verify how your IS NULL queries execute. Don't assume an index will be used—the query planner makes cost-based decisions. If IS NULL performance is critical, consider partial indexes or restructuring data to avoid the NULL pattern entirely.

IS NULL Patterns and Idioms

Over decades of SQL development, certain IS NULL patterns have emerged as best practices. These idioms solve common problems elegantly and are immediately recognizable to experienced SQL developers.

is_null_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- PATTERN 1: Find incomplete records
-- Useful for data quality checks and workflow management
SELECT *
FROM orders
WHERE shipped_date IS NULL
   OR tracking_number IS NULL
   OR delivery_date IS NULL;
 
-- PATTERN 2: Prioritize NULL values (unprocessed first)
-- Common in job queues and workflow systems
SELECT *
FROM tasks
ORDER BY 
    CASE WHEN assigned_to IS NULL THEN 0 ELSE 1 END,
    priority DESC,
    created_at ASC;
 
-- PATTERN 3: Conditional aggregation with NULL detection
-- Count records by NULL/non-NULL status
SELECT 
    COUNT(*) AS total_orders,
    COUNT(shipped_date) AS shipped_orders,
    COUNT(*) - COUNT(shipped_date) AS pending_orders,
    -- Or using CASE:
    SUM(CASE WHEN shipped_date IS NULL THEN 1 ELSE 0 END) AS pending_v2
FROM orders;
 
-- PATTERN 4: Soft delete detection
-- Common pattern where deleted_at IS NULL means active
SELECT *
FROM users
WHERE deleted_at IS NULL;  -- Only active users
 
SELECT *
FROM users
WHERE deleted_at IS NOT NULL;  -- Only deleted users
 
-- PATTERN 5: Self-referential hierarchy root
-- Find top-level nodes (no parent)
SELECT *
FROM categories
WHERE parent_category_id IS NULL;  -- Root categories
 
SELECT *
FROM employees
WHERE manager_id IS NULL;  -- CEO/top-level managers
 
-- PATTERN 6: Orphan detection with anti-join
-- Find child records whose parent was deleted
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;  -- Orders with missing customers
 
-- PATTERN 7: Latest record per group (NULL for "current")
-- When end_date IS NULL means currently active
SELECT *
FROM price_history
WHERE end_date IS NULL;  -- Current prices only
 
SELECT *
FROM employment_history
WHERE termination_date IS NULL;  -- Current employees
 
-- PATTERN 8: Composite NULL check
-- Ensure at least one contact method exists
SELECT *
FROM contacts
WHERE email IS NULL 
  AND phone IS NULL 
  AND address IS NULL;
-- Find contacts with NO contact information at all

Pattern Recognition

These patterns appear constantly in production databases. The soft-delete pattern (deleted_at IS NULL) and temporal validity pattern (end_date IS NULL) are especially common in enterprise applications. Recognizing these patterns helps you understand unfamiliar schemas quickly.

IS NULL in Different SQL Clauses

IS NULL can appear in various parts of a SQL statement, each with specific use cases and behaviors.

IS NULL Usage Across SQL Clauses
Clause	Usage	Example
WHERE	Filter rows with NULL values	`WHERE col IS NULL`
HAVING	Filter groups with NULL aggregate results	`HAVING SUM(col) IS NULL`
ON (JOIN)	Complex join conditions with NULL handling	`ON a.col = b.col OR (a.col IS NULL AND b.col IS NULL)`
CASE	Conditional logic based on NULL	`CASE WHEN col IS NULL THEN 'N/A' ELSE col END`
CHECK	Constraint validation (with care)	`CHECK (status IS NOT NULL OR pending = TRUE)`

is_null_clauses.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- IS NULL in HAVING clause
-- Find departments where no employee has commission
SELECT department_id, department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING MAX(e.commission_rate) IS NULL;
 
-- IS NULL in JOIN condition for NULL-safe matching
-- Match records where values are equal OR both are NULL
SELECT a.*, b.*
FROM table_a a
JOIN table_b b 
    ON (a.key = b.key) 
    OR (a.key IS NULL AND b.key IS NULL);
 
-- IS NULL in CASE expression
SELECT 
    employee_id,
    first_name,
    last_name,
    CASE 
        WHEN middle_name IS NULL THEN first_name || ' ' || last_name
        ELSE first_name || ' ' || middle_name || ' ' || last_name
    END AS full_name,
    CASE 
        WHEN commission_rate IS NULL THEN 'Not applicable'
        WHEN commission_rate = 0 THEN 'Zero'
        ELSE CAST(commission_rate AS VARCHAR) || '%'
    END AS commission_display
FROM employees;
 
-- IS NULL in CHECK constraint
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20),
    shipped_date DATE,
    -- If shipped, must have a date; if not shipped, must have no date
    CONSTRAINT chk_shipping 
        CHECK (
            (status = 'shipped' AND shipped_date IS NOT NULL)
            OR (status <> 'shipped' AND shipped_date IS NULL)
        )
);
 
-- IS NULL in subquery WHERE
SELECT *
FROM products
WHERE category_id IN (
    SELECT category_id 
    FROM categories 
    WHERE parent_id IS NULL  -- Find products in root categories
);
 
-- IS NULL with EXISTS (finding non-existence)
SELECT d.*
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary IS NOT NULL  -- And has a defined salary
);

Summary: Mastering IS NULL

We've thoroughly explored the IS NULL predicate—the correct and only way to test for NULL values in SQL. Let's consolidate the key insights:

Key Takeaways

•IS NULL is the only correct way to test for NULL values. Using = NULL silently returns zero rows.
•IS NULL returns TRUE or FALSE definitively, never UNKNOWN—making it safe for WHERE clauses.
•IS NULL works on any expression including arithmetic, functions, subqueries, and CASE results.
•LEFT JOIN + IS NULL is the anti-join pattern for finding rows without matches—a fundamental SQL technique.
•Index usage with IS NULL depends on NULL density and column position. Use EXPLAIN to verify performance.
•Standard patterns like soft delete (deleted_at IS NULL), hierarchy roots (parent_id IS NULL), and temporal validity (end_date IS NULL) appear constantly in production databases.

What's next:

Now that we've mastered detecting NULL with IS NULL, we'll explore its complement: IS NOT NULL. While it might seem like a simple negation, IS NOT NULL has its own patterns, performance characteristics, and use cases that warrant dedicated attention.

Page Complete

You now understand IS NULL thoroughly—from basic syntax to complex expressions, join patterns, and performance optimization. IS NULL is a fundamental tool that you'll use constantly in SQL development. Next, we'll examine IS NOT NULL with equal depth.

2 / 5

Loading learning content...

Database Management SystemsNULL Handling

NULL Handling in SQL

LevelBeginner

Duration50 mins

TopicNULL Handling

2 / 5

IS NULL: Detecting Missing Values

The One True Way to Check for NULL

So how do we actually detect NULL values in our data?

This page explores IS NULL in exhaustive detail: its syntax, behavior in different contexts, common patterns, and the subtle distinctions that separate correct NULL handling from broken queries.

What You Will Learn

IS NULL Fundamentals

Syntax:

expression IS NULL

Behavior:

If expression evaluates to NULL → Returns TRUE
If expression evaluates to any non-NULL value → Returns FALSE

This binary behavior is crucial: IS NULL gives you a clear, predictable answer that works correctly in WHERE clauses.

is_null_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Basic IS NULL examples
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),       -- Nullable
    phone VARCHAR(20),             -- Nullable
    manager_id INT,                -- Nullable (CEO has no manager)
    commission_rate DECIMAL(5,2)   -- Nullable (non-sales have no commission)
);
 
-- Find employees without a middle name
SELECT employee_id, first_name, last_name
FROM employees
WHERE middle_name IS NULL;
 
-- Find employees without a phone number on record
SELECT employee_id, first_name, last_name
FROM employees
WHERE phone IS NULL;
 
-- Find employees without a manager (typically CEO or top-level)
SELECT employee_id, first_name, last_name
FROM employees
WHERE manager_id IS NULL;
 
-- Find employees who are NOT on commission
-- (sales staff would have a commission_rate value)
SELECT employee_id, first_name, last_name
FROM employees
WHERE commission_rate IS NULL;
 
-- Combine with other conditions
-- Find employees hired after 2020 who don't have phones
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2020-01-01'
  AND phone IS NULL;

Always Prefer IS NULL Over Alternatives

IS NULL vs = NULL: The Critical Difference

Let's examine exactly why = NULL fails and IS NULL succeeds. Understanding this difference at a deep level prevents subtle bugs.

Using = NULL (WRONG)

•Treats NULL as a value to compare against other values
•Returns UNKNOWN when either side is NULL
•WHERE clause excludes UNKNOWN rows as if they were FALSE
•Silent failure: No error, just zero results
•Hard to debug: Query looks syntactically correct

Using IS NULL (CORRECT)

•Special predicate designed specifically for NULL testing
•Returns TRUE or FALSE definitively—never UNKNOWN
•WHERE clause includes TRUE rows correctly
•Correct behavior: Returns rows where column is NULL
•Self-documenting: Clear intent to check for NULL

is_null_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Sample data
CREATE TABLE test_null (
    id INT PRIMARY KEY,
    value INT
);
 
INSERT INTO test_null VALUES 
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 100);
 
-- WRONG: This returns ZERO rows
SELECT * FROM test_null WHERE value = NULL;
-- For row id=2: NULL = NULL → UNKNOWN → excluded
-- For row id=4: NULL = NULL → UNKNOWN → excluded
-- For row id=1: 100 = NULL → UNKNOWN → excluded
-- ALL rows are excluded because ALL comparisons yield UNKNOWN!
 
-- CORRECT: This returns rows 2 and 4
SELECT * FROM test_null WHERE value IS NULL;
-- For row id=2: NULL IS NULL → TRUE → included
-- For row id=4: NULL IS NULL → TRUE → included
-- For row id=1, 3, 5: value IS NULL → FALSE → excluded
 
-- Another common mistake:
-- Trying to check if two columns both contain NULL
CREATE TABLE pairs (
    id INT,
    col_a INT,
    col_b INT
);
 
INSERT INTO pairs VALUES 
(1, NULL, NULL),
(2, NULL, 10),
(3, 10, NULL),
(4, 10, 10);
 
-- WRONG: Returns zero rows (both NULLs don't match with =)
SELECT * FROM pairs WHERE col_a = col_b;
 
-- To find rows where both columns are NULL:
SELECT * FROM pairs 
WHERE col_a IS NULL AND col_b IS NULL;
 
-- To find rows where columns have the same value OR both are NULL:
SELECT * FROM pairs 
WHERE col_a = col_b 
   OR (col_a IS NULL AND col_b IS NULL);

Why Doesn't SQL Just Make = NULL Work?

IS NULL in Complex Expressions

IS NULL can be applied to any expression, not just simple column references. This includes arithmetic expressions, function results, subqueries, and CASE expressions.

is_null_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- IS NULL with arithmetic expressions
-- NULL propagates through arithmetic, so we can detect it
SELECT product_id, price, discount
FROM products
WHERE (price - discount) IS NULL;
-- Returns products where either price OR discount is NULL
-- (since NULL - anything = NULL and anything - NULL = NULL)
 
-- IS NULL with function results
SELECT customer_id, first_name, last_name
FROM customers
WHERE TRIM(middle_name) IS NULL;
-- TRIM(NULL) returns NULL
 
-- IS NULL with concatenation
-- In most databases: NULL || 'string' = NULL
SELECT employee_id, first_name, middle_name, last_name
FROM employees  
WHERE (first_name || ' ' || middle_name || ' ' || last_name) IS NULL;
-- This would catch any NULL in any of the name columns
 
-- IS NULL with CASE expressions
SELECT 
    product_id,
    product_name,
    CASE 
        WHEN stock_quantity > 100 THEN 'High'
        WHEN stock_quantity > 10 THEN 'Medium'
        WHEN stock_quantity > 0 THEN 'Low'
        -- No ELSE clause means NULL when stock_quantity is 0 or NULL
    END AS stock_level
FROM products
WHERE (
    CASE 
        WHEN stock_quantity > 100 THEN 'High'
        WHEN stock_quantity > 10 THEN 'Medium'
        WHEN stock_quantity > 0 THEN 'Low'
    END
) IS NULL;
 
-- IS NULL with scalar subqueries
SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
WHERE (
    SELECT d.department_name 
    FROM departments d 
    WHERE d.department_id = e.department_id
) IS NULL;
-- Finds employees whose department_id doesn't match any department
-- (subquery returns NULL, which IS NULL detects)

Expression IS NULL vs Column IS NULL

IS NULL with Aggregate Functions:

Aggregate functions have special NULL handling, but their results can still be tested with IS NULL:

is_null_aggregates.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- When does an aggregate return NULL?
 
-- SUM, AVG, MIN, MAX return NULL when applied to empty set
SELECT SUM(amount) FROM orders WHERE 1 = 0;  -- Returns NULL
SELECT AVG(amount) FROM orders WHERE 1 = 0;  -- Returns NULL
 
-- COUNT never returns NULL (returns 0 for empty set)
SELECT COUNT(*) FROM orders WHERE 1 = 0;     -- Returns 0
SELECT COUNT(amount) FROM orders WHERE 1 = 0; -- Returns 0
 
-- Using IS NULL with HAVING to find groups with no data
SELECT 
    department_id,
    SUM(bonus) as total_bonus
FROM employees
GROUP BY department_id
HAVING SUM(bonus) IS NULL;
-- Finds departments where ALL employees have NULL bonus
-- (SUM of all NULLs = NULL)
 
-- Practical example: Find product categories with no sales
SELECT 
    c.category_id,
    c.category_name,
    SUM(oi.quantity * oi.unit_price) as total_revenue
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_id, c.category_name
HAVING SUM(oi.quantity * oi.unit_price) IS NULL;

IS NULL with JOIN Operations

IS NULL becomes particularly powerful when combined with outer joins. It allows you to find rows that don't match across tables—a common and important query pattern.

is_null_joins.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Classic anti-join pattern: Find customers with no orders
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
 
-- Why this works:
-- LEFT JOIN keeps all customers, even without matching orders
-- For customers with no orders, all order columns are NULL
-- WHERE o.order_id IS NULL filters to just those unmatched rows
 
-- Find products never ordered
SELECT p.product_id, p.product_name, p.unit_price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;
 
-- Find employees with no direct reports
SELECT m.employee_id, m.first_name, m.last_name
FROM employees m
LEFT JOIN employees r ON m.employee_id = r.manager_id
WHERE r.employee_id IS NULL;
-- Uses self-join: m is potential managers, r is potential reports
 
-- Find departments with no employees
SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
 
-- Multi-table anti-join: Find categories with no products that have orders
SELECT c.category_id, c.category_name
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

Anti-Join vs NOT EXISTS vs NOT IN

FULL OUTER JOIN with IS NULL:

FULL OUTER JOIN combined with IS NULL can identify unmatched rows on both sides of a join—useful for data reconciliation and finding orphaned records.

full_outer_join_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Find mismatches between two systems' customer records
-- (e.g., CRM vs Order System)
SELECT 
    crm.customer_id AS crm_id,
    crm.customer_name AS crm_name,
    orders.customer_id AS order_id,
    orders.customer_name AS order_name,
    CASE 
        WHEN crm.customer_id IS NULL 
            THEN 'Only in Order System'
        WHEN orders.customer_id IS NULL 
            THEN 'Only in CRM'
        ELSE 'In Both'
    END AS status
FROM crm_customers crm
FULL OUTER JOIN order_system_customers orders 
    ON crm.customer_id = orders.customer_id
WHERE crm.customer_id IS NULL 
   OR orders.customer_id IS NULL;
 
-- This query identifies:
-- 1. Customers in CRM but not Order System (orders.customer_id IS NULL)
-- 2. Customers in Order System but not CRM (crm.customer_id IS NULL)
 
-- Audit pattern: Compare staging table to production
SELECT 
    COALESCE(s.record_id, p.record_id) AS record_id,
    s.value AS staging_value,
    p.value AS production_value,
    CASE 
        WHEN s.record_id IS NULL THEN 'DELETE (in prod, not staging)'
        WHEN p.record_id IS NULL THEN 'INSERT (in staging, not prod)'
        WHEN s.value <> p.value THEN 'UPDATE (values differ)'
        ELSE 'UNCHANGED'
    END AS action_needed
FROM staging_table s
FULL OUTER JOIN production_table p ON s.record_id = p.record_id
WHERE s.record_id IS NULL 
   OR p.record_id IS NULL
   OR s.value <> p.value;

IS NULL Performance Considerations

IS NULL queries have specific performance characteristics that database developers should understand. Index usage, query planning, and NULL density all affect how quickly these queries execute.

IS NULL Performance Factors

•Index Usage — Most databases can use indexes for IS NULL conditions, but behavior varies. B-tree indexes typically include NULL values; the query planner decides whether an index scan is worthwhile.
•NULL Density — If 99% of rows have NULL in a column, an index on that column may not help IS NULL queries (the optimizer may choose a table scan instead). Conversely, if only 1% are NULL, an index can dramatically speed up IS NULL.
•Leading Column Position — For multi-column indexes, IS NULL works best when the NULL-checked column is the leading (leftmost) column. A trailing column IS NULL check may not use the index effectively.
•Index-Only Scans — If your query only needs columns that are all in the index, the database can answer IS NULL without touching the main table—a significant performance win.
•Partial Indexes — Some databases (PostgreSQL, SQLite) support partial indexes that include only rows where a condition is true. You can create an index on rows where a column IS NOT NULL, which is smaller and faster.

is_null_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Example: Analyzing IS NULL performance
 
-- Create a table with varying NULL density
CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20),
    processed_date TIMESTAMP,  -- NULL for unprocessed records
    amount DECIMAL(10,2)
);
 
-- Index on the nullable column
CREATE INDEX idx_processed_date ON large_table(processed_date);
 
-- This query may or may not use the index depending on NULL density
EXPLAIN ANALYZE
SELECT * FROM large_table WHERE processed_date IS NULL;
 
-- PostgreSQL: Create a partial index for unprocessed records
-- This is smaller and faster than a full index
CREATE INDEX idx_unprocessed ON large_table(id) 
WHERE processed_date IS NULL;
 
-- Now this query efficiently uses the partial index
SELECT * FROM large_table WHERE processed_date IS NULL;
 
-- Covering index for IS NULL query (index-only scan)
CREATE INDEX idx_covering ON large_table(processed_date, id, status);
 
-- This query can be answered entirely from the index
SELECT id, status FROM large_table WHERE processed_date IS NULL;
 
-- Composite index with NULL column
CREATE INDEX idx_status_processed 
    ON large_table(status, processed_date);
 
-- This uses the index (status is leading, equals condition)
SELECT * FROM large_table 
WHERE status = 'pending' AND processed_date IS NULL;
 
-- This may not use the index well (status not constrained)
SELECT * FROM large_table WHERE processed_date IS NULL;

Check Your Execution Plan

IS NULL Patterns and Idioms

Over decades of SQL development, certain IS NULL patterns have emerged as best practices. These idioms solve common problems elegantly and are immediately recognizable to experienced SQL developers.

is_null_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- PATTERN 1: Find incomplete records
-- Useful for data quality checks and workflow management
SELECT *
FROM orders
WHERE shipped_date IS NULL
   OR tracking_number IS NULL
   OR delivery_date IS NULL;
 
-- PATTERN 2: Prioritize NULL values (unprocessed first)
-- Common in job queues and workflow systems
SELECT *
FROM tasks
ORDER BY 
    CASE WHEN assigned_to IS NULL THEN 0 ELSE 1 END,
    priority DESC,
    created_at ASC;
 
-- PATTERN 3: Conditional aggregation with NULL detection
-- Count records by NULL/non-NULL status
SELECT 
    COUNT(*) AS total_orders,
    COUNT(shipped_date) AS shipped_orders,
    COUNT(*) - COUNT(shipped_date) AS pending_orders,
    -- Or using CASE:
    SUM(CASE WHEN shipped_date IS NULL THEN 1 ELSE 0 END) AS pending_v2
FROM orders;
 
-- PATTERN 4: Soft delete detection
-- Common pattern where deleted_at IS NULL means active
SELECT *
FROM users
WHERE deleted_at IS NULL;  -- Only active users
 
SELECT *
FROM users
WHERE deleted_at IS NOT NULL;  -- Only deleted users
 
-- PATTERN 5: Self-referential hierarchy root
-- Find top-level nodes (no parent)
SELECT *
FROM categories
WHERE parent_category_id IS NULL;  -- Root categories
 
SELECT *
FROM employees
WHERE manager_id IS NULL;  -- CEO/top-level managers
 
-- PATTERN 6: Orphan detection with anti-join
-- Find child records whose parent was deleted
SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;  -- Orders with missing customers
 
-- PATTERN 7: Latest record per group (NULL for "current")
-- When end_date IS NULL means currently active
SELECT *
FROM price_history
WHERE end_date IS NULL;  -- Current prices only
 
SELECT *
FROM employment_history
WHERE termination_date IS NULL;  -- Current employees
 
-- PATTERN 8: Composite NULL check
-- Ensure at least one contact method exists
SELECT *
FROM contacts
WHERE email IS NULL 
  AND phone IS NULL 
  AND address IS NULL;
-- Find contacts with NO contact information at all

Pattern Recognition

IS NULL in Different SQL Clauses

IS NULL can appear in various parts of a SQL statement, each with specific use cases and behaviors.

IS NULL Usage Across SQL Clauses
Clause	Usage	Example
WHERE	Filter rows with NULL values	`WHERE col IS NULL`
HAVING	Filter groups with NULL aggregate results	`HAVING SUM(col) IS NULL`
ON (JOIN)	Complex join conditions with NULL handling	`ON a.col = b.col OR (a.col IS NULL AND b.col IS NULL)`
CASE	Conditional logic based on NULL	`CASE WHEN col IS NULL THEN 'N/A' ELSE col END`
CHECK	Constraint validation (with care)	`CHECK (status IS NOT NULL OR pending = TRUE)`

is_null_clauses.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- IS NULL in HAVING clause
-- Find departments where no employee has commission
SELECT department_id, department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING MAX(e.commission_rate) IS NULL;
 
-- IS NULL in JOIN condition for NULL-safe matching
-- Match records where values are equal OR both are NULL
SELECT a.*, b.*
FROM table_a a
JOIN table_b b 
    ON (a.key = b.key) 
    OR (a.key IS NULL AND b.key IS NULL);
 
-- IS NULL in CASE expression
SELECT 
    employee_id,
    first_name,
    last_name,
    CASE 
        WHEN middle_name IS NULL THEN first_name || ' ' || last_name
        ELSE first_name || ' ' || middle_name || ' ' || last_name
    END AS full_name,
    CASE 
        WHEN commission_rate IS NULL THEN 'Not applicable'
        WHEN commission_rate = 0 THEN 'Zero'
        ELSE CAST(commission_rate AS VARCHAR) || '%'
    END AS commission_display
FROM employees;
 
-- IS NULL in CHECK constraint
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20),
    shipped_date DATE,
    -- If shipped, must have a date; if not shipped, must have no date
    CONSTRAINT chk_shipping 
        CHECK (
            (status = 'shipped' AND shipped_date IS NOT NULL)
            OR (status <> 'shipped' AND shipped_date IS NULL)
        )
);
 
-- IS NULL in subquery WHERE
SELECT *
FROM products
WHERE category_id IN (
    SELECT category_id 
    FROM categories 
    WHERE parent_id IS NULL  -- Find products in root categories
);
 
-- IS NULL with EXISTS (finding non-existence)
SELECT d.*
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary IS NOT NULL  -- And has a defined salary
);

Summary: Mastering IS NULL

We've thoroughly explored the IS NULL predicate—the correct and only way to test for NULL values in SQL. Let's consolidate the key insights:

Key Takeaways

•IS NULL is the only correct way to test for NULL values. Using = NULL silently returns zero rows.
•IS NULL returns TRUE or FALSE definitively, never UNKNOWN—making it safe for WHERE clauses.
•IS NULL works on any expression including arithmetic, functions, subqueries, and CASE results.
•LEFT JOIN + IS NULL is the anti-join pattern for finding rows without matches—a fundamental SQL technique.
•Index usage with IS NULL depends on NULL density and column position. Use EXPLAIN to verify performance.
•Standard patterns like soft delete (deleted_at IS NULL), hierarchy roots (parent_id IS NULL), and temporal validity (end_date IS NULL) appear constantly in production databases.

What's next:

Page Complete

2 / 5