IS NOT NULL: Confirming Data… — NULL Handling in SQL | OneNoughtOne

0/131

Database Management SystemsNULL Handling

NULL Handling in SQL

LevelBeginner

Duration50 mins

TopicNULL Handling

3 / 5

IS NOT NULL: Confirming Data Presence

The Positive Assertion: Data Exists

While IS NULL helps us find missing data, IS NOT NULL serves the equally important purpose of confirming that data exists . This might seem like a trivial negation of IS NULL, but IS NOT NULL has its own distinct use cases, patterns, and optimization considerations. When you write WHERE column IS NOT NULL, you're making a positive assertion: this column has an actual value . This assertion is fundamental to countless query patterns—from filtering out incomplete records to ensuring join conditions work correctly to validating data quality. In this page, we'll explore IS NOT NULL with the same rigor we applied to IS NULL, understanding not just how it works, but when and why you should use it.

What You Will Learn

By the end of this page, you will master the IS NOT NULL predicate—understanding its syntax, the critical difference from <> NULL, its role in data validation, performance optimization through filtering, and common patterns for working with complete, present data.

IS NOT NULL Fundamentals

The IS NOT NULL predicate tests whether an expression has an actual value (as opposed to NULL). Like IS NULL, it returns a definite TRUE or FALSE—never UNKNOWN. Syntax: expression IS NOT NULL Behavior: If expression evaluates to any non-NULL value \to Returns TRUE If expression evaluates to NULL \to Returns FALSE This definite boolean result makes IS NOT NULL safe for use in WHERE clauses, WHERE only TRUE rows pass through.

is_not_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
39
40
41
42
43
44
45
-- Basic IS NOT 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
    commission_rate DECIMAL(5,2)   -- Nullable
);
 
-- Find employees who HAVE a middle name
SELECT employee_id, first_name, middle_name, last_name
FROM employees
WHERE middle_name IS NOT NULL;
 
-- Find employees who HAVE provided a phone number
SELECT employee_id, first_name, last_name, phone
FROM employees
WHERE phone IS NOT NULL;
 
-- Find employees who HAVE a manager (excludes CEO/top-level)
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NOT NULL;
 
-- Find employees who ARE on commission (sales staff)
SELECT employee_id, first_name, last_name, commission_rate
FROM employees
WHERE commission_rate IS NOT NULL;
 
-- Combine multiple IS NOT NULL checks
-- Find employees with complete contact information
SELECT employee_id, first_name, last_name, phone, email
FROM employees
WHERE phone IS NOT NULL
  AND email IS NOT NULL;
 
-- Find fully documented employees
SELECT *
FROM employees
WHERE phone IS NOT NULL
  AND emergency_contact IS NOT NULL
  AND address IS NOT NULL
  AND ssn IS NOT NULL;

IS NOT NULL as a Data Quality Filter

IS NOT NULL is often your first line of defense against incomplete data. Before performing calculations or joins, filtering with IS NOT NULL ensures you're working with actual values. This prevents NULL propagation from polluting your results.

IS NOT NULL vs <> NULL: Another Critical Difference

Just as = NULL doesn't work for finding NULLs, <> NULL doesn't work for finding non-NULLs. The same three-valued logic applies, with the same silent failure behavior.

Using <> NULL (WRONG)

•value <> NULL → UNKNOWN for ALL values
•Whether value is 100, 0, 'hello', or NULL
•UNKNOWN is excluded from WHERE results
•Query returns zero rows every time
•Same silent failure as = NULL

Using IS NOT NULL (CORRECT)

•value IS NOT NULL → TRUE or FALSE
•TRUE when value is any actual value
•FALSE when value is NULL
•WHERE correctly filters to non-NULL rows
•Expected behavior every time

is_not_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
-- Sample data
CREATE TABLE test_values (
    id INT PRIMARY KEY,
    value INT
);
 
INSERT INTO test_values VALUES 
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 0);
 
-- WRONG: This returns ZERO rows (not 3 as expected)
SELECT * FROM test_values WHERE value <> NULL;
-- For id=1: 100 <> NULL → UNKNOWN → excluded
-- For id=2: NULL <> NULL → UNKNOWN → excluded  
-- For id=3: 200 <> NULL → UNKNOWN → excluded
-- For id=4: NULL <> NULL → UNKNOWN → excluded
-- For id=5: 0 <> NULL → UNKNOWN → excluded
-- ALL rows return UNKNOWN, so ALL are excluded!
 
-- CORRECT: This returns rows 1, 3, 5
SELECT * FROM test_values WHERE value IS NOT NULL;
-- For id=1: 100 IS NOT NULL → TRUE → included
-- For id=2: NULL IS NOT NULL → FALSE → excluded
-- For id=3: 200 IS NOT NULL → TRUE → included
-- For id=4: NULL IS NOT NULL → FALSE → excluded
-- For id=5: 0 IS NOT NULL → TRUE → included
 
-- Note: 0 IS NOT NULL → TRUE
-- Zero is a value, not NULL!
 
-- Common combination: Check for non-NULL AND specific condition
-- WRONG approach (checking for non-null implicitly)
SELECT * FROM test_values WHERE value > 50;
-- This works for positive case, but is unclear about NULL handling
 
-- EXPLICIT approach (clearer intent)
SELECT * FROM test_values 
WHERE value IS NOT NULL 
  AND value > 50;
-- Same result, but explicitly shows we want non-NULL values > 50

The != NULL Variant

Some developers use!= NULL instead of <> NULL . Both are equally wrong for the same reasons. The not-equal operator, regardless of syntax (<> or!=), treats NULL as a value to compare against, which always yields UNKNOWN. Always use IS NOT NULL.

IS NOT NULL in Data Validation

IS NOT NULL is a cornerstone of data validation and quality assurance. Before performing operations that assume data exists, explicit NULL checks prevent errors and incorrect results.

is_not_null_validation.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
73
74
75
76
77
78
79
80
81
82
83
-- DATA VALIDATION: Ensure required fields are populated
 
-- Validate before INSERT/UPDATE via CHECK constraint
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    ship_date DATE,
    status VARCHAR(20) NOT NULL,
    
    -- If status is 'shipped', ship_date must exist
    CONSTRAINT chk_shipped_date CHECK (
        status <> 'shipped' OR ship_date IS NOT NULL
    )
);
 
-- Data quality report: Find records missing required info
SELECT 
    'customers' AS table_name,
    customer_id,
    'email' AS missing_field
FROM customers
WHERE email IS NULL
 
UNION ALL
 
SELECT 
    'customers',
    customer_id,
    'phone'
FROM customers
WHERE phone IS NULL
 
UNION ALL
 
SELECT 
    'orders',
    order_id,
    'shipping_address'
FROM orders
WHERE shipping_address IS NULL
  AND status = 'pending_shipment';
 
-- Completeness percentage report
SELECT 
    'email' AS field,
    COUNT(*) AS total_rows,
    COUNT(email) AS non_null_count,
    ROUND(100.0 * COUNT(email) / COUNT(*), 2) AS completeness_pct
FROM customers
 
UNION ALL
 
SELECT 
    'phone',
    COUNT(*),
    COUNT(phone),
    ROUND(100.0 * COUNT(phone) / COUNT(*), 2)
FROM customers
 
UNION ALL
 
SELECT 
    'address',
    COUNT(*),
    COUNT(address),
    ROUND(100.0 * COUNT(address) / COUNT(*), 2)
FROM customers;
 
-- Pre-calculation validation
-- Ensure we have all required data before running reports
SELECT 
    CASE 
        WHEN COUNT(*) = COUNT(unit_price) 
         AND COUNT(*) = COUNT(quantity)
         AND COUNT(*) = COUNT(discount)
        THEN 'All pricing data present'
        ELSE 'WARNING: Missing pricing data'
    END AS validation_status,
    COUNT(*) - COUNT(unit_price) AS missing_prices,
    COUNT(*) - COUNT(quantity) AS missing_quantities,
    COUNT(*) - COUNT(discount) AS missing_discounts
FROM order_items;

Defensive Query Writing

When writing queries that perform calculations, consider explicitly filtering with IS NOT NULL even if you expect all values to be present. This makes your assumptions explicit and prevents surprises when data quality issues exist. It's defensive programming applied to SQL.

Validating Before Calculations: NULL propagates through arithmetic operations. If any operand is NULL, the result is NULL. Explicit IS NOT NULL checks prevent these silent failures.

is_not_null_calculations.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
-- Without validation: NULL values silently corrupt results
SELECT 
    order_id,
    quantity * unit_price AS line_total  -- NULL if either is NULL
FROM order_items;
 
-- With validation: Exclude incomplete records explicitly
SELECT 
    order_id,
    quantity * unit_price AS line_total
FROM order_items
WHERE quantity IS NOT NULL 
  AND unit_price IS NOT NULL;
 
-- With validation + reporting: Show both valid and invalid
SELECT 
    order_id,
    CASE 
        WHEN quantity IS NOT NULL AND unit_price IS NOT NULL
        THEN quantity * unit_price
        ELSE NULL
    END AS line_total,
    CASE 
        WHEN quantity IS NULL OR unit_price IS NULL
        THEN 'INCOMPLETE DATA'
        ELSE 'OK'
    END AS data_status
FROM order_items;
 
-- Aggregate with explicit validation
SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    AVG(salary) AS avg_salary,  -- Only includes non-NULL
    SUM(salary) / COUNT(*) AS avg_including_null_as_zero  -- Different!
FROM employees
WHERE salary IS NOT NULL  -- Ensures we only consider employees with salaries
GROUP BY department_id;

IS NOT NULL for Query Optimization

IS NOT NULL can significantly impact query performance, both directly through index usage and indirectly by reducing the dataset early in query execution.

Performance Benefits of IS NOT NULL

•Early Filtering — Placing IS NOT NULL early in WHERE reduces rows for subsequent operations. The query planner can push this filter down to table scans.
•Index Utilization — Many indexes exclude NULL values or handle them specially. IS NOT NULL conditions can help the optimizer choose index scans over sequential scans.
•Join Optimization — Filtering NULL foreign keys before joins prevents unnecessary comparisons. A row with NULL FK can never match, so filtering it helps.
•Aggregate Efficiency — When you know you want non-NULL values, filtering before aggregation is more efficient than letting the aggregate ignore NULLs.
•Partial Index Matching — If you have a partial index on WHERE column IS NOT NULL, explicitly adding this condition allows the optimizer to use it.

is_not_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
42
43
44
45
46
47
-- Optimization: Filter NULLs before expensive operations
 
-- SUBOPTIMAL: JOIN includes NULL foreign keys
SELECT o.*, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- If o.customer_id is sometimes NULL, these rows are processed
-- by the join only to find no match
 
-- OPTIMIZED: Exclude NULL FKs before JOIN
SELECT o.*, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.customer_id IS NOT NULL;
-- Or, the optimizer might do this automatically
 
-- Optimization: Partial index usage
-- Create index only for non-NULL values
CREATE INDEX idx_orders_ship_date 
    ON orders(ship_date) 
    WHERE ship_date IS NOT NULL;  -- PostgreSQL partial index
 
-- This query uses the partial index
SELECT * FROM orders
WHERE ship_date IS NOT NULL
  AND ship_date > '2024-01-01';
 
-- Optimization: Pre-filter for aggregates
-- SUBOPTIMAL: AVG ignores NULLs, but scans them
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
 
-- OPTIMIZED: Exclude NULLs before grouping (if that's the intent)
SELECT department_id, AVG(salary)
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id;
-- Now the GROUP BY processes fewer rows
 
-- Optimization: Materialized view with IS NOT NULL
CREATE MATERIALIZED VIEW active_subscriptions AS
SELECT * FROM subscriptions
WHERE cancelled_at IS NULL  -- Only active
  AND plan_id IS NOT NULL;  -- With valid plans
 
-- Queries against this view are pre-filtered

Optimizer Intelligence

Modern query optimizers are sophisticated. They may automatically infer IS NOT NULL conditions from other predicates (e.g., WHERE column = 5 implies column IS NOT NULL). However, explicit IS NOT NULL can help when the optimizer doesn't make this inference, and it makes your intent clear to human readers.

IS NOT NULL Patterns and Use Cases

IS NOT NULL enables several essential query patterns. Understanding these patterns helps you write idiomatic SQL that other developers recognize instantly.

is_not_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
73
74
75
76
77
78
-- PATTERN 1: Find complete records
-- Records with all required optional fields filled in
SELECT c.*
FROM customers c
WHERE c.email IS NOT NULL
  AND c.phone IS NOT NULL
  AND c.address IS NOT NULL;
 
-- PATTERN 2: Existence proof in optional relationships
-- Find orders that HAVE been assigned to a warehouse
SELECT o.*
FROM orders o
WHERE o.warehouse_id IS NOT NULL;  -- Assigned to warehouse
 
-- PATTERN 3: Filter valid candidates for processing
-- Find records ready for next pipeline stage
SELECT *
FROM invoices
WHERE approved_by IS NOT NULL      -- Has been approved
  AND sent_date IS NULL;            -- But not yet sent
 
-- PATTERN 4: Ensure calculation safety
SELECT 
    product_id,
    product_name,
    (price - cost) / price * 100 AS margin_pct
FROM products
WHERE price IS NOT NULL 
  AND cost IS NOT NULL
  AND price > 0;  -- Also prevent division by zero
 
-- PATTERN 5: Non-orphan children
-- Find child records that still have parent
SELECT oi.*
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE oi.order_id IS NOT NULL;  -- Has valid parent reference
 
-- PATTERN 6: Active period check (temporal pattern)
-- Records currently in effect (started, not ended)
SELECT *
FROM price_changes
WHERE effective_from IS NOT NULL   -- Has started
  AND effective_to IS NULL;        -- Hasn't ended
 
-- PATTERN 7: Data quality gate
-- Count records passing quality standards
SELECT 
    COUNT(*) AS total_products,
    SUM(CASE WHEN price IS NOT NULL 
             AND category_id IS NOT NULL
             AND description IS NOT NULL
        THEN 1 ELSE 0 END) AS quality_products,
    100.0 * SUM(CASE WHEN price IS NOT NULL 
                     AND category_id IS NOT NULL
                     AND description IS NOT NULL
                THEN 1 ELSE 0 END) / COUNT(*) AS quality_pct
FROM products;
 
-- PATTERN 8: Segmentation by data completeness
SELECT 
    CASE 
        WHEN phone IS NOT NULL AND email IS NOT NULL 
            THEN 'Full Contact'
        WHEN phone IS NOT NULL OR email IS NOT NULL 
            THEN 'Partial Contact'
        ELSE 'No Contact'
    END AS contact_tier,
    COUNT(*) AS customer_count
FROM customers
GROUP BY 
    CASE 
        WHEN phone IS NOT NULL AND email IS NOT NULL 
            THEN 'Full Contact'
        WHEN phone IS NOT NULL OR email IS NOT NULL 
            THEN 'Partial Contact'
        ELSE 'No Contact'
    END;

Combine with Business Logic

IS NOT NULL often combines with business logic conditions. For example, 'Has been approved AND not yet processed' is approved_at IS NOT NULL AND processed_at IS NULL . These combinations form the backbone of workflow and state management queries.

IS NOT NULL in CHECK Constraints

IS NOT NULL can be used within CHECK constraints to enforce conditional data requirements—situations where a field is required only under certain circumstances.

is_not_null_constraints.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
73
74
75
76
77
78
79
80
81
82
83
84
85
-- Conditional required fields using IS NOT NULL in CHECK
 
-- If order is shipped, ship_date and tracking_number are required
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    ship_date DATE,
    tracking_number VARCHAR(50),
    
    CONSTRAINT chk_shipped_info CHECK (
        status <> 'shipped' 
        OR (ship_date IS NOT NULL AND tracking_number IS NOT NULL)
    )
);
 
-- If employee is on commission, commission_rate is required
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    is_sales BOOLEAN DEFAULT FALSE,
    commission_rate DECIMAL(5,2),
    
    CONSTRAINT chk_sales_commission CHECK (
        is_sales = FALSE 
        OR commission_rate IS NOT NULL
    )
);
 
-- Mutual exclusivity: exactly one of two fields must be set
CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    credit_card_id INT,
    bank_account_id INT,
    amount DECIMAL(10,2) NOT NULL,
    
    -- Exactly one payment method required
    CONSTRAINT chk_payment_method CHECK (
        (credit_card_id IS NOT NULL AND bank_account_id IS NULL)
        OR (credit_card_id IS NULL AND bank_account_id IS NOT NULL)
    )
);
 
-- At least one contact method required
CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    
    CONSTRAINT chk_has_contact_method CHECK (
        email IS NOT NULL 
        OR phone IS NOT NULL 
        OR address IS NOT NULL
    )
);
 
-- Graduated requirements based on status
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    approved_by INT,
    approved_date DATE,
    started_by INT,
    start_date DATE,
    completed_by INT,
    completion_date DATE,
    
    -- Draft: no approvals needed
    -- Approved: must have approval info
    -- Started: must have approval and start info  
    -- Completed: must have all info
    CONSTRAINT chk_status_requirements CHECK (
        (status = 'draft')
        OR (status = 'approved' AND approved_by IS NOT NULL AND approved_date IS NOT NULL)
        OR (status = 'started' AND approved_by IS NOT NULL AND approved_date IS NOT NULL
            AND started_by IS NOT NULL AND start_date IS NOT NULL)
        OR (status = 'completed' AND approved_by IS NOT NULL AND approved_date IS NOT NULL
            AND started_by IS NOT NULL AND start_date IS NOT NULL
            AND completed_by IS NOT NULL AND completion_date IS NOT NULL)
    )
);

CHECK Constraints and NULL

Remember: CHECK constraints pass when the condition is TRUE or UNKNOWN. If your CHECK constraint evaluates to UNKNOWN (due to NULL), the row is allowed. This is why we use IS NOT NULL explicitly in checks—we want definite TRUE/FALSE evaluation, not UNKNOWN.

NOT (column IS NULL) vs IS NOT NULL

SQL provides two syntactically equivalent ways to check for non-NULL values. Understanding their equivalence confirms the language's logical consistency.

not_is_null_equivalence.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
-- These are logically equivalent:
SELECT * FROM employees WHERE salary IS NOT NULL;
SELECT * FROM employees WHERE NOT (salary IS NULL);
 
-- Proof of equivalence:
-- For non-NULL salary:
--   salary IS NULL = FALSE
--   NOT FALSE = TRUE  
--   salary IS NOT NULL = TRUE ✓
 
-- For NULL salary:
--   salary IS NULL = TRUE
--   NOT TRUE = FALSE
--   salary IS NOT NULL = FALSE ✓
 
-- The IS NOT NULL form is preferred because:
-- 1. More concise (one predicate vs two)
-- 2. More readable (standard SQL idiom)
-- 3. Potentially better optimized (single predicate)
 
-- Complex example where NOT becomes useful:
-- Find employees who don't meet the "complete" criteria
SELECT * FROM employees
WHERE NOT (
    phone IS NOT NULL 
    AND email IS NOT NULL 
    AND address IS NOT NULL
);
 
-- Equivalent but more verbose:
SELECT * FROM employees
WHERE phone IS NULL 
   OR email IS NULL 
   OR address IS NULL;

Style Preference

Always prefer IS NOT NULL over NOT (column IS NULL) . The former is the idiomatic SQL style and is immediately recognized by all SQL developers. Reserve the NOT (...) form for complex boolean expressions where the negation encompasses multiple conditions.

Comparison of NULL-checking Predicates
Expression	When TRUE	When FALSE / Does Not Match
column IS NULL	column is NULL	column has any value
column IS NOT NULL	column has any value	column is NULL
NOT (column IS NULL)	column has any value (= IS NOT NULL)	column is NULL
column = NULL	Never (always UNKNOWN)	N/A
column <> NULL	Never (always UNKNOWN)	N/A

Database Management SystemsNULL Handling

NULL Handling in SQL

LevelBeginner

Duration50 mins

TopicNULL Handling

3 / 5

IS NOT NULL: Confirming Data Presence

The Positive Assertion: Data Exists

While IS NULL helps us find missing data, IS NOT NULL serves the equally important purpose of confirming that data exists . This might seem like a trivial negation of IS NULL, but IS NOT NULL has its own distinct use cases, patterns, and optimization considerations. When you write WHERE column IS NOT NULL, you're making a positive assertion: this column has an actual value . This assertion is fundamental to countless query patterns—from filtering out incomplete records to ensuring join conditions work correctly to validating data quality. In this page, we'll explore IS NOT NULL with the same rigor we applied to IS NULL, understanding not just how it works, but when and why you should use it.

What You Will Learn

By the end of this page, you will master the IS NOT NULL predicate—understanding its syntax, the critical difference from <> NULL, its role in data validation, performance optimization through filtering, and common patterns for working with complete, present data.

IS NOT NULL Fundamentals

The IS NOT NULL predicate tests whether an expression has an actual value (as opposed to NULL). Like IS NULL, it returns a definite TRUE or FALSE—never UNKNOWN. Syntax: expression IS NOT NULL Behavior: If expression evaluates to any non-NULL value \to Returns TRUE If expression evaluates to NULL \to Returns FALSE This definite boolean result makes IS NOT NULL safe for use in WHERE clauses, WHERE only TRUE rows pass through.

is_not_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
39
40
41
42
43
44
45
-- Basic IS NOT 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
    commission_rate DECIMAL(5,2)   -- Nullable
);
 
-- Find employees who HAVE a middle name
SELECT employee_id, first_name, middle_name, last_name
FROM employees
WHERE middle_name IS NOT NULL;
 
-- Find employees who HAVE provided a phone number
SELECT employee_id, first_name, last_name, phone
FROM employees
WHERE phone IS NOT NULL;
 
-- Find employees who HAVE a manager (excludes CEO/top-level)
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NOT NULL;
 
-- Find employees who ARE on commission (sales staff)
SELECT employee_id, first_name, last_name, commission_rate
FROM employees
WHERE commission_rate IS NOT NULL;
 
-- Combine multiple IS NOT NULL checks
-- Find employees with complete contact information
SELECT employee_id, first_name, last_name, phone, email
FROM employees
WHERE phone IS NOT NULL
  AND email IS NOT NULL;
 
-- Find fully documented employees
SELECT *
FROM employees
WHERE phone IS NOT NULL
  AND emergency_contact IS NOT NULL
  AND address IS NOT NULL
  AND ssn IS NOT NULL;

IS NOT NULL as a Data Quality Filter

IS NOT NULL is often your first line of defense against incomplete data. Before performing calculations or joins, filtering with IS NOT NULL ensures you're working with actual values. This prevents NULL propagation from polluting your results.

IS NOT NULL vs <> NULL: Another Critical Difference

Just as = NULL doesn't work for finding NULLs, <> NULL doesn't work for finding non-NULLs. The same three-valued logic applies, with the same silent failure behavior.

Using <> NULL (WRONG)

•value <> NULL → UNKNOWN for ALL values
•Whether value is 100, 0, 'hello', or NULL
•UNKNOWN is excluded from WHERE results
•Query returns zero rows every time
•Same silent failure as = NULL

Using IS NOT NULL (CORRECT)

•value IS NOT NULL → TRUE or FALSE
•TRUE when value is any actual value
•FALSE when value is NULL
•WHERE correctly filters to non-NULL rows
•Expected behavior every time

is_not_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
-- Sample data
CREATE TABLE test_values (
    id INT PRIMARY KEY,
    value INT
);
 
INSERT INTO test_values VALUES 
(1, 100),
(2, NULL),
(3, 200),
(4, NULL),
(5, 0);
 
-- WRONG: This returns ZERO rows (not 3 as expected)
SELECT * FROM test_values WHERE value <> NULL;
-- For id=1: 100 <> NULL → UNKNOWN → excluded
-- For id=2: NULL <> NULL → UNKNOWN → excluded  
-- For id=3: 200 <> NULL → UNKNOWN → excluded
-- For id=4: NULL <> NULL → UNKNOWN → excluded
-- For id=5: 0 <> NULL → UNKNOWN → excluded
-- ALL rows return UNKNOWN, so ALL are excluded!
 
-- CORRECT: This returns rows 1, 3, 5
SELECT * FROM test_values WHERE value IS NOT NULL;
-- For id=1: 100 IS NOT NULL → TRUE → included
-- For id=2: NULL IS NOT NULL → FALSE → excluded
-- For id=3: 200 IS NOT NULL → TRUE → included
-- For id=4: NULL IS NOT NULL → FALSE → excluded
-- For id=5: 0 IS NOT NULL → TRUE → included
 
-- Note: 0 IS NOT NULL → TRUE
-- Zero is a value, not NULL!
 
-- Common combination: Check for non-NULL AND specific condition
-- WRONG approach (checking for non-null implicitly)
SELECT * FROM test_values WHERE value > 50;
-- This works for positive case, but is unclear about NULL handling
 
-- EXPLICIT approach (clearer intent)
SELECT * FROM test_values 
WHERE value IS NOT NULL 
  AND value > 50;
-- Same result, but explicitly shows we want non-NULL values > 50

The != NULL Variant

Some developers use!= NULL instead of <> NULL . Both are equally wrong for the same reasons. The not-equal operator, regardless of syntax (<> or!=), treats NULL as a value to compare against, which always yields UNKNOWN. Always use IS NOT NULL.

IS NOT NULL in Data Validation

IS NOT NULL is a cornerstone of data validation and quality assurance. Before performing operations that assume data exists, explicit NULL checks prevent errors and incorrect results.

is_not_null_validation.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
73
74
75
76
77
78
79
80
81
82
83
-- DATA VALIDATION: Ensure required fields are populated
 
-- Validate before INSERT/UPDATE via CHECK constraint
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    ship_date DATE,
    status VARCHAR(20) NOT NULL,
    
    -- If status is 'shipped', ship_date must exist
    CONSTRAINT chk_shipped_date CHECK (
        status <> 'shipped' OR ship_date IS NOT NULL
    )
);
 
-- Data quality report: Find records missing required info
SELECT 
    'customers' AS table_name,
    customer_id,
    'email' AS missing_field
FROM customers
WHERE email IS NULL
 
UNION ALL
 
SELECT 
    'customers',
    customer_id,
    'phone'
FROM customers
WHERE phone IS NULL
 
UNION ALL
 
SELECT 
    'orders',
    order_id,
    'shipping_address'
FROM orders
WHERE shipping_address IS NULL
  AND status = 'pending_shipment';
 
-- Completeness percentage report
SELECT 
    'email' AS field,
    COUNT(*) AS total_rows,
    COUNT(email) AS non_null_count,
    ROUND(100.0 * COUNT(email) / COUNT(*), 2) AS completeness_pct
FROM customers
 
UNION ALL
 
SELECT 
    'phone',
    COUNT(*),
    COUNT(phone),
    ROUND(100.0 * COUNT(phone) / COUNT(*), 2)
FROM customers
 
UNION ALL
 
SELECT 
    'address',
    COUNT(*),
    COUNT(address),
    ROUND(100.0 * COUNT(address) / COUNT(*), 2)
FROM customers;
 
-- Pre-calculation validation
-- Ensure we have all required data before running reports
SELECT 
    CASE 
        WHEN COUNT(*) = COUNT(unit_price) 
         AND COUNT(*) = COUNT(quantity)
         AND COUNT(*) = COUNT(discount)
        THEN 'All pricing data present'
        ELSE 'WARNING: Missing pricing data'
    END AS validation_status,
    COUNT(*) - COUNT(unit_price) AS missing_prices,
    COUNT(*) - COUNT(quantity) AS missing_quantities,
    COUNT(*) - COUNT(discount) AS missing_discounts
FROM order_items;

Defensive Query Writing

When writing queries that perform calculations, consider explicitly filtering with IS NOT NULL even if you expect all values to be present. This makes your assumptions explicit and prevents surprises when data quality issues exist. It's defensive programming applied to SQL.

Validating Before Calculations: NULL propagates through arithmetic operations. If any operand is NULL, the result is NULL. Explicit IS NOT NULL checks prevent these silent failures.

is_not_null_calculations.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
-- Without validation: NULL values silently corrupt results
SELECT 
    order_id,
    quantity * unit_price AS line_total  -- NULL if either is NULL
FROM order_items;
 
-- With validation: Exclude incomplete records explicitly
SELECT 
    order_id,
    quantity * unit_price AS line_total
FROM order_items
WHERE quantity IS NOT NULL 
  AND unit_price IS NOT NULL;
 
-- With validation + reporting: Show both valid and invalid
SELECT 
    order_id,
    CASE 
        WHEN quantity IS NOT NULL AND unit_price IS NOT NULL
        THEN quantity * unit_price
        ELSE NULL
    END AS line_total,
    CASE 
        WHEN quantity IS NULL OR unit_price IS NULL
        THEN 'INCOMPLETE DATA'
        ELSE 'OK'
    END AS data_status
FROM order_items;
 
-- Aggregate with explicit validation
SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    AVG(salary) AS avg_salary,  -- Only includes non-NULL
    SUM(salary) / COUNT(*) AS avg_including_null_as_zero  -- Different!
FROM employees
WHERE salary IS NOT NULL  -- Ensures we only consider employees with salaries
GROUP BY department_id;

IS NOT NULL for Query Optimization

IS NOT NULL can significantly impact query performance, both directly through index usage and indirectly by reducing the dataset early in query execution.

Performance Benefits of IS NOT NULL

•Early Filtering — Placing IS NOT NULL early in WHERE reduces rows for subsequent operations. The query planner can push this filter down to table scans.
•Index Utilization — Many indexes exclude NULL values or handle them specially. IS NOT NULL conditions can help the optimizer choose index scans over sequential scans.
•Join Optimization — Filtering NULL foreign keys before joins prevents unnecessary comparisons. A row with NULL FK can never match, so filtering it helps.
•Aggregate Efficiency — When you know you want non-NULL values, filtering before aggregation is more efficient than letting the aggregate ignore NULLs.
•Partial Index Matching — If you have a partial index on WHERE column IS NOT NULL, explicitly adding this condition allows the optimizer to use it.

is_not_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
42
43
44
45
46
47
-- Optimization: Filter NULLs before expensive operations
 
-- SUBOPTIMAL: JOIN includes NULL foreign keys
SELECT o.*, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- If o.customer_id is sometimes NULL, these rows are processed
-- by the join only to find no match
 
-- OPTIMIZED: Exclude NULL FKs before JOIN
SELECT o.*, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.customer_id IS NOT NULL;
-- Or, the optimizer might do this automatically
 
-- Optimization: Partial index usage
-- Create index only for non-NULL values
CREATE INDEX idx_orders_ship_date 
    ON orders(ship_date) 
    WHERE ship_date IS NOT NULL;  -- PostgreSQL partial index
 
-- This query uses the partial index
SELECT * FROM orders
WHERE ship_date IS NOT NULL
  AND ship_date > '2024-01-01';
 
-- Optimization: Pre-filter for aggregates
-- SUBOPTIMAL: AVG ignores NULLs, but scans them
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
 
-- OPTIMIZED: Exclude NULLs before grouping (if that's the intent)
SELECT department_id, AVG(salary)
FROM employees
WHERE salary IS NOT NULL
GROUP BY department_id;
-- Now the GROUP BY processes fewer rows
 
-- Optimization: Materialized view with IS NOT NULL
CREATE MATERIALIZED VIEW active_subscriptions AS
SELECT * FROM subscriptions
WHERE cancelled_at IS NULL  -- Only active
  AND plan_id IS NOT NULL;  -- With valid plans
 
-- Queries against this view are pre-filtered

Optimizer Intelligence

Modern query optimizers are sophisticated. They may automatically infer IS NOT NULL conditions from other predicates (e.g., WHERE column = 5 implies column IS NOT NULL). However, explicit IS NOT NULL can help when the optimizer doesn't make this inference, and it makes your intent clear to human readers.

IS NOT NULL Patterns and Use Cases

IS NOT NULL enables several essential query patterns. Understanding these patterns helps you write idiomatic SQL that other developers recognize instantly.

is_not_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
73
74
75
76
77
78
-- PATTERN 1: Find complete records
-- Records with all required optional fields filled in
SELECT c.*
FROM customers c
WHERE c.email IS NOT NULL
  AND c.phone IS NOT NULL
  AND c.address IS NOT NULL;
 
-- PATTERN 2: Existence proof in optional relationships
-- Find orders that HAVE been assigned to a warehouse
SELECT o.*
FROM orders o
WHERE o.warehouse_id IS NOT NULL;  -- Assigned to warehouse
 
-- PATTERN 3: Filter valid candidates for processing
-- Find records ready for next pipeline stage
SELECT *
FROM invoices
WHERE approved_by IS NOT NULL      -- Has been approved
  AND sent_date IS NULL;            -- But not yet sent
 
-- PATTERN 4: Ensure calculation safety
SELECT 
    product_id,
    product_name,
    (price - cost) / price * 100 AS margin_pct
FROM products
WHERE price IS NOT NULL 
  AND cost IS NOT NULL
  AND price > 0;  -- Also prevent division by zero
 
-- PATTERN 5: Non-orphan children
-- Find child records that still have parent
SELECT oi.*
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE oi.order_id IS NOT NULL;  -- Has valid parent reference
 
-- PATTERN 6: Active period check (temporal pattern)
-- Records currently in effect (started, not ended)
SELECT *
FROM price_changes
WHERE effective_from IS NOT NULL   -- Has started
  AND effective_to IS NULL;        -- Hasn't ended
 
-- PATTERN 7: Data quality gate
-- Count records passing quality standards
SELECT 
    COUNT(*) AS total_products,
    SUM(CASE WHEN price IS NOT NULL 
             AND category_id IS NOT NULL
             AND description IS NOT NULL
        THEN 1 ELSE 0 END) AS quality_products,
    100.0 * SUM(CASE WHEN price IS NOT NULL 
                     AND category_id IS NOT NULL
                     AND description IS NOT NULL
                THEN 1 ELSE 0 END) / COUNT(*) AS quality_pct
FROM products;
 
-- PATTERN 8: Segmentation by data completeness
SELECT 
    CASE 
        WHEN phone IS NOT NULL AND email IS NOT NULL 
            THEN 'Full Contact'
        WHEN phone IS NOT NULL OR email IS NOT NULL 
            THEN 'Partial Contact'
        ELSE 'No Contact'
    END AS contact_tier,
    COUNT(*) AS customer_count
FROM customers
GROUP BY 
    CASE 
        WHEN phone IS NOT NULL AND email IS NOT NULL 
            THEN 'Full Contact'
        WHEN phone IS NOT NULL OR email IS NOT NULL 
            THEN 'Partial Contact'
        ELSE 'No Contact'
    END;

Combine with Business Logic

IS NOT NULL often combines with business logic conditions. For example, 'Has been approved AND not yet processed' is approved_at IS NOT NULL AND processed_at IS NULL . These combinations form the backbone of workflow and state management queries.

IS NOT NULL in CHECK Constraints

IS NOT NULL can be used within CHECK constraints to enforce conditional data requirements—situations where a field is required only under certain circumstances.

is_not_null_constraints.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
73
74
75
76
77
78
79
80
81
82
83
84
85
-- Conditional required fields using IS NOT NULL in CHECK
 
-- If order is shipped, ship_date and tracking_number are required
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    ship_date DATE,
    tracking_number VARCHAR(50),
    
    CONSTRAINT chk_shipped_info CHECK (
        status <> 'shipped' 
        OR (ship_date IS NOT NULL AND tracking_number IS NOT NULL)
    )
);
 
-- If employee is on commission, commission_rate is required
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    is_sales BOOLEAN DEFAULT FALSE,
    commission_rate DECIMAL(5,2),
    
    CONSTRAINT chk_sales_commission CHECK (
        is_sales = FALSE 
        OR commission_rate IS NOT NULL
    )
);
 
-- Mutual exclusivity: exactly one of two fields must be set
CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    credit_card_id INT,
    bank_account_id INT,
    amount DECIMAL(10,2) NOT NULL,
    
    -- Exactly one payment method required
    CONSTRAINT chk_payment_method CHECK (
        (credit_card_id IS NOT NULL AND bank_account_id IS NULL)
        OR (credit_card_id IS NULL AND bank_account_id IS NOT NULL)
    )
);
 
-- At least one contact method required
CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    
    CONSTRAINT chk_has_contact_method CHECK (
        email IS NOT NULL 
        OR phone IS NOT NULL 
        OR address IS NOT NULL
    )
);
 
-- Graduated requirements based on status
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    approved_by INT,
    approved_date DATE,
    started_by INT,
    start_date DATE,
    completed_by INT,
    completion_date DATE,
    
    -- Draft: no approvals needed
    -- Approved: must have approval info
    -- Started: must have approval and start info  
    -- Completed: must have all info
    CONSTRAINT chk_status_requirements CHECK (
        (status = 'draft')
        OR (status = 'approved' AND approved_by IS NOT NULL AND approved_date IS NOT NULL)
        OR (status = 'started' AND approved_by IS NOT NULL AND approved_date IS NOT NULL
            AND started_by IS NOT NULL AND start_date IS NOT NULL)
        OR (status = 'completed' AND approved_by IS NOT NULL AND approved_date IS NOT NULL
            AND started_by IS NOT NULL AND start_date IS NOT NULL
            AND completed_by IS NOT NULL AND completion_date IS NOT NULL)
    )
);

CHECK Constraints and NULL

Remember: CHECK constraints pass when the condition is TRUE or UNKNOWN. If your CHECK constraint evaluates to UNKNOWN (due to NULL), the row is allowed. This is why we use IS NOT NULL explicitly in checks—we want definite TRUE/FALSE evaluation, not UNKNOWN.

NOT (column IS NULL) vs IS NOT NULL

SQL provides two syntactically equivalent ways to check for non-NULL values. Understanding their equivalence confirms the language's logical consistency.

not_is_null_equivalence.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
-- These are logically equivalent:
SELECT * FROM employees WHERE salary IS NOT NULL;
SELECT * FROM employees WHERE NOT (salary IS NULL);
 
-- Proof of equivalence:
-- For non-NULL salary:
--   salary IS NULL = FALSE
--   NOT FALSE = TRUE  
--   salary IS NOT NULL = TRUE ✓
 
-- For NULL salary:
--   salary IS NULL = TRUE
--   NOT TRUE = FALSE
--   salary IS NOT NULL = FALSE ✓
 
-- The IS NOT NULL form is preferred because:
-- 1. More concise (one predicate vs two)
-- 2. More readable (standard SQL idiom)
-- 3. Potentially better optimized (single predicate)
 
-- Complex example where NOT becomes useful:
-- Find employees who don't meet the "complete" criteria
SELECT * FROM employees
WHERE NOT (
    phone IS NOT NULL 
    AND email IS NOT NULL 
    AND address IS NOT NULL
);
 
-- Equivalent but more verbose:
SELECT * FROM employees
WHERE phone IS NULL 
   OR email IS NULL 
   OR address IS NULL;

Style Preference

Always prefer IS NOT NULL over NOT (column IS NULL) . The former is the idiomatic SQL style and is immediately recognized by all SQL developers. Reserve the NOT (...) form for complex boolean expressions where the negation encompasses multiple conditions.

Comparison of NULL-checking Predicates
Expression	When TRUE	When FALSE / Does Not Match
column IS NULL	column is NULL	column has any value
column IS NOT NULL	column has any value	column is NULL
NOT (column IS NULL)	column has any value (= IS NOT NULL)	column is NULL
column = NULL	Never (always UNKNOWN)	N/A
column <> NULL	Never (always UNKNOWN)	N/A

IS NOT NULL: Confirming Data Presence

The Positive Assertion: Data Exists

IS NOT NULL Fundamentals

IS NOT NULL vs <> NULL: Another Critical Difference

IS NOT NULL in Data Validation

IS NOT NULL for Query Optimization

IS NOT NULL Patterns and Use Cases

IS NOT NULL in CHECK Constraints

NOT (column IS NULL) vs IS NOT NULL

There’s more to understand.

IS NOT NULL: Confirming Data Presence

The Positive Assertion: Data Exists

IS NOT NULL Fundamentals

IS NOT NULL vs <> NULL: Another Critical Difference

IS NOT NULL in Data Validation

IS NOT NULL for Query Optimization

IS NOT NULL Patterns and Use Cases

IS NOT NULL in CHECK Constraints

NOT (column IS NULL) vs IS NOT NULL

There’s more to understand.