IS NULL: Detecting Missing Values

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;

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);

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)

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;

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;

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;

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;

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

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
);