Attributes And Domains - Learning Module

Loading content...

0/241

NULL Values

The Billion-Dollar Unknown

In 2009, Tony Hoare—inventor of the null reference in programming languages—called it his "billion-dollar mistake." The database world's NULL is a close relative, and its behavior has caused untold confusion, bugs, and debates since E.F. Codd introduced it to handle missing information in the relational model.

NULL is not a value—it is a marker indicating the absence of a value. This seemingly simple concept has profound implications: it breaks boolean logic, complicates comparisons, affects aggregates unexpectedly, and introduces the infamous three-valued logic (TRUE, FALSE, UNKNOWN).

Mastering NULL is non-negotiable for anyone working with relational databases. Failure to understand NULL leads to subtle bugs that pass tests but fail in production, aggregations that silently exclude data, and queries that return unexpected results.

What You Will Learn

By the end of this page, you will understand what NULL represents, the different interpretations of NULL, three-valued logic and its implications, how NULL affects comparisons and operations, NULL handling in aggregates and grouping, and best practices for NULL-aware database design.

What NULL Represents

Critical Understanding:

NULL is not zero. NULL is not an empty string. NULL is not false. NULL is not a value at all—it is a special marker that indicates the absence of a value.

$$\text{NULL} \neq 0, \quad \text{NULL} \neq '', \quad \text{NULL} \neq \text{FALSE}, \quad \text{NULL} \neq \text{NULL}$$

Yes, you read that last one correctly: NULL does not equal NULL. This is because we cannot assert that two unknown values are the same.

NULL vs False vs Zero vs Empty
Value	Type	Represents	Example
`NULL`	Marker (no type)	Unknown or inapplicable	`middle_name = NULL` (person has no middle name)
`0`	Integer	The numeric value zero	`items_in_cart = 0` (cart is empty but known)
`''`	String	Empty string (zero length)	`nickname = ''` (explicitly no nickname)
`FALSE`	Boolean	The logical false value	`is_active = FALSE` (definitely inactive)

The Most Common NULL Mistake

Writing WHERE column = NULL NEVER returns any rows. NULL cannot be equated to anything, including itself. You MUST use WHERE column IS NULL or WHERE column IS NOT NULL. This is the #1 NULL-related bug in database code.

null-basics.sql
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
-- Create test data
CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    middle_name     VARCHAR(50),         -- Nullable: may not have middle name
    manager_id      INT,                  -- Nullable: CEO has no manager
    termination_date DATE                 -- Nullable: still employed
);
 
INSERT INTO employee VALUES
    (1, 'Alice', 'Marie', NULL, NULL),    -- CEO, no manager, still employed
    (2, 'Bob', NULL, 1, NULL),            -- No middle name, reports to Alice
    (3, 'Charlie', 'James', 1, '2023-06-15'); -- Terminated
 
-- WRONG: This finds NOTHING! NULL cannot be compared with =
SELECT * FROM employee WHERE middle_name = NULL;
-- Returns: 0 rows (always)
 
-- CORRECT: Use IS NULL
SELECT * FROM employee WHERE middle_name IS NULL;
-- Returns: Bob (employee_id = 2)
 
-- WRONG: This doesn't exclude NULLs as expected
SELECT * FROM employee WHERE middle_name <> 'Marie';
-- Returns: Charlie only! Bob (with NULL middle_name) is excluded!
-- Because NULL <> 'Marie' evaluates to UNKNOWN, not TRUE
 
-- CORRECT: Explicitly handle NULL
SELECT * FROM employee WHERE middle_name <> 'Marie' OR middle_name IS NULL;
-- Returns: Bob and Charlie
 
-- NULL does not equal NULL
SELECT 1 WHERE NULL = NULL;
-- Returns: 0 rows
 
SELECT 1 WHERE NULL IS NULL;
-- Returns: 1 row

Interpretations of NULL

NULL can represent several distinct situations, and SQL makes no distinction between them—they're all just NULL. This ambiguity is both a feature and a limitation of the relational model.

The Three Meanings of NULL

•Unknown Value: The value exists but is not known. Example: A customer's phone number that wasn't collected during registration.
•Inapplicable / Not Applicable: The attribute doesn't apply to this entity. Example: spouse_name for an unmarried person, termination_date for a current employee.
•Withheld / Not Disclosed: The value exists and is known, but is not being shared. Example: Salary in a public employee directory.

The Semantic Ambiguity Problem:

Consider phone_number = NULL. Does this mean:

The customer has a phone but didn't provide the number? (Unknown)
The customer genuinely has no phone? (Inapplicable)
The customer refused to provide their number? (Withheld)

SQL cannot distinguish these cases—they all appear as NULL. This limits what queries can express.

Codd's Proposal:

E.F. Codd later proposed distinguishing between types of NULL:

A-mark: Applicable but missing (unknown)
I-mark: Inapplicable (doesn't apply)

This would have introduced four-valued logic, but was never widely implemented. Most databases use a single NULL with three-valued logic.

Design Around NULL Ambiguity

When NULL's ambiguity matters, add explicit indicator columns. Instead of phone_number = NULL meaning everything, use additional columns like has_phone BOOLEAN, phone_verified BOOLEAN, or phone_disclosure_status VARCHAR. This moves semantics from implicit (NULL) to explicit (data).

null-interpretations.sql
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
-- PROBLEM: Ambiguous NULLs
CREATE TABLE customer_ambiguous (
    customer_id     INT PRIMARY KEY,
    phone_number    VARCHAR(20)     -- NULL means... what exactly?
);
 
-- Better: Explicit semantics
CREATE TABLE customer_explicit (
    customer_id         INT PRIMARY KEY,
    phone_number        VARCHAR(20),
    has_phone           BOOLEAN NOT NULL DEFAULT TRUE,
    phone_provided      BOOLEAN NOT NULL DEFAULT FALSE,
    phone_verified      BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Business rules enforced
    CONSTRAINT check_phone_logic CHECK (
        (phone_provided = FALSE AND phone_number IS NULL) OR
        (phone_provided = TRUE AND phone_number IS NOT NULL)
    )
);
 
-- Now we can distinguish:
-- No phone: has_phone = FALSE, phone_number = NULL
-- Has phone but not provided: has_phone = TRUE, phone_provided = FALSE, phone_number = NULL
-- Phone provided: has_phone = TRUE, phone_provided = TRUE, phone_number = '555-1234'
 
-- Another common pattern: Use status columns
CREATE TABLE employee_with_status (
    employee_id         INT PRIMARY KEY,
    name                VARCHAR(100) NOT NULL,
    
    -- Instead of NULL termination_date meaning "still employed"
    termination_date    DATE,
    employment_status   VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (employment_status IN ('active', 'terminated', 'on_leave', 'retired'))
);
 
-- Query active employees explicitly, not by NULL checking
SELECT * FROM employee_with_status WHERE employment_status = 'active';
 
-- Pattern for optional relationships: Junction table vs nullable FK
-- Option 1: Nullable FK (NULL = no manager)
CREATE TABLE emp_nullable_fk (
    emp_id      INT PRIMARY KEY,
    manager_id  INT REFERENCES emp_nullable_fk(emp_id)  -- NULL for CEO
);
 
-- Option 2: Separate junction table (no NULLs, cleaner semantics)
CREATE TABLE emp_no_nullable (
    emp_id      INT PRIMARY KEY,
    name        VARCHAR(100)
);
 
CREATE TABLE emp_manager (
    emp_id      INT PRIMARY KEY REFERENCES emp_no_nullable(emp_id),
    manager_id  INT NOT NULL REFERENCES emp_no_nullable(emp_id)
);
-- Employees with no manager simply have no row in emp_manager

Three-Valued Logic (3VL)

NULL introduces three-valued logic into SQL: every boolean expression evaluates to TRUE, FALSE, or UNKNOWN. This seemingly small addition has far-reaching consequences for query behavior.

The UNKNOWN Value:

When any comparison involves NULL, the result is UNKNOWN (not TRUE, not FALSE):

5 = NULL → UNKNOWN
NULL = NULL → UNKNOWN
NULL > 10 → UNKNOWN
NULL AND TRUE → UNKNOWN
NULL OR FALSE → UNKNOWN

The WHERE clause only returns rows where the condition is TRUE. Rows with UNKNOWN conditions are filtered out—behaving like FALSE.

Truth Tables for 3VL:

AND Truth Table (3VL)
AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

OR Truth Table (3VL)
OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

NOT Truth Table (3VL)
Input	NOT Output
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

The NOT NULL Trap

NOT UNKNOWN is still UNKNOWN, not TRUE! This means NOT (x = NULL) is UNKNOWN, not TRUE. You cannot use negation to test for non-NULL values. Always use explicit IS NOT NULL checks.

three-valued-logic.sql
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
-- Setup
CREATE TABLE test_null (
    id      INT PRIMARY KEY,
    val     INT
);
 
INSERT INTO test_null VALUES (1, 10), (2, 20), (3, NULL);
 
-- DEMO: Three-valued logic in action
 
-- This finds rows where val = 10 is TRUE
SELECT * FROM test_null WHERE val = 10;
-- Result: id=1
 
-- This finds rows where val = 10 is NOT TRUE (FALSE or UNKNOWN)
SELECT * FROM test_null WHERE NOT (val = 10);
-- Result: id=2 only! id=3 excluded because NOT UNKNOWN = UNKNOWN
 
-- To include NULL values, be explicit
SELECT * FROM test_null WHERE val <> 10 OR val IS NULL;
-- Result: id=2 and id=3
 
-- Boolean expression evaluation
SELECT 
    id,
    val,
    CASE WHEN val = 10 THEN 'TRUE' ELSE 'NOT TRUE' END AS equals_10,
    CASE WHEN val IS NULL THEN 'UNKNOWN' 
         WHEN val = 10 THEN 'TRUE' 
         ELSE 'FALSE' END AS explicit_3vl
FROM test_null;
 
-- Comparison chain: All return UNKNOWN when NULL is involved
SELECT 
    NULL = NULL AS eq_null,           -- NULL (UNKNOWN)
    NULL <> NULL AS neq_null,         -- NULL (UNKNOWN)
    NULL > NULL AS gt_null,           -- NULL (UNKNOWN)
    NULL AND TRUE AS and_true,        -- NULL (UNKNOWN)
    NULL OR FALSE AS or_false,        -- NULL (UNKNOWN)
    NOT NULL AS not_null_expr;        -- NULL (UNKNOWN)
 
-- The only way to test for NULL
SELECT 
    NULL IS NULL AS is_null,          -- TRUE
    NULL IS NOT NULL AS is_not_null;  -- FALSE

NULL Behavior in Operations

NULL propagates through most operations—when you perform arithmetic, string operations, or comparisons involving NULL, the result is typically NULL or UNKNOWN.

NULL Propagation in Operations
Operation	Expression	Result	Explanation
Arithmetic	`5 + NULL`	`NULL`	Any arithmetic with NULL yields NULL
Arithmetic	`NULL * 0`	`NULL`	Even multiplication by 0!
String	`'Hello' \|\| NULL`	`NULL`	Concatenation with NULL is NULL
Comparison	`10 > NULL`	`UNKNOWN`	Cannot compare to unknown
IN list	`5 IN (1, 2, NULL)`	`UNKNOWN` (if no match)	NULL in list causes uncertainty
BETWEEN	`NULL BETWEEN 1 AND 10`	`UNKNOWN`	Cannot range-test unknown
LIKE	`NULL LIKE 'A%'`	`UNKNOWN`	Cannot pattern-match unknown
Function	`LENGTH(NULL)`	`NULL` (usually)	Most functions return NULL for NULL input

null-in-operations.sql
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
-- Arithmetic: NULL propagates
SELECT 
    10 + NULL AS add_null,           -- NULL
    10 * NULL AS mult_null,          -- NULL
    10 / NULL AS div_null,           -- NULL
    NULL * 0 AS mult_zero,           -- NULL (not 0!)
    NULL - NULL AS sub_null;         -- NULL
 
-- String operations: NULL propagates
SELECT 
    'Hello' || NULL AS concat_null,           -- NULL (standard SQL)
    CONCAT('Hello', NULL) AS concat_func,     -- 'Hello' in some DBs (varies!)
    LENGTH(NULL) AS len_null,                 -- NULL
    UPPER(NULL) AS upper_null;                -- NULL
 
-- Careful with CONCAT behavior - database-specific!
-- PostgreSQL || operator: NULL propagates
-- MySQL CONCAT: tolerates NULLs better
 
-- IN with NULL - tricky behavior
SELECT 5 IN (1, 2, 3);           -- FALSE
SELECT 5 IN (1, 2, 5);           -- TRUE
SELECT 5 IN (1, 2, NULL);        -- UNKNOWN (might be in there!)
SELECT NULL IN (1, 2, 3);        -- UNKNOWN
SELECT NULL IN (1, 2, NULL);     -- UNKNOWN (even with NULL in list!)
 
-- NOT IN with NULL - the classic trap!
SELECT 5 NOT IN (1, 2, 3);       -- TRUE
SELECT 5 NOT IN (1, 2, NULL);    -- UNKNOWN! Not TRUE!
 
-- This query returns NO ROWS if any subquery result is NULL:
-- SELECT * FROM products 
-- WHERE product_id NOT IN (SELECT product_id FROM discontinued)
-- If discontinued table has any NULL product_id, query returns nothing!
 
-- Safe alternative: Use NOT EXISTS or explicit NULL handling
-- SELECT * FROM products p
-- WHERE NOT EXISTS (
--     SELECT 1 FROM discontinued d WHERE d.product_id = p.product_id
-- )
 
-- COALESCE: Replace NULL with a default
SELECT 
    COALESCE(NULL, 'default') AS col1,        -- 'default'
    COALESCE(NULL, NULL, 'third') AS col2,    -- 'third'
    COALESCE('first', 'second') AS col3;      -- 'first'
 
-- NULLIF: Create NULL when values match
SELECT 
    NULLIF(10, 10) AS eq_null,         -- NULL (10 = 10)
    NULLIF(10, 20) AS neq_value;       -- 10 (10 <> 20)
 
-- Practical use: Avoid division by zero
SELECT total_cost / NULLIF(quantity, 0) AS unit_cost  -- NULL instead of error
FROM orders;

The NOT IN NULL Trap

WHERE x NOT IN (1, 2, NULL) returns NO ROWS if x is any value! Because x NOT IN (1, 2, NULL) requires x <> 1 AND x <> 2 AND x <> NULL. The last condition is UNKNOWN, making the entire AND expression UNKNOWN. Use NOT EXISTS or ensure no NULLs in the subquery.

NULL in Aggregate Functions

Aggregate functions have special NULL handling that often surprises developers. The general rule: aggregates ignore NULL values (with COUNT(*) as a notable exception).

Aggregate Function NULL Behavior
Function	NULL Handling	Example
`COUNT(*)`	Counts all rows, including those with NULLs	`COUNT(*)` on 3 rows = 3
`COUNT(column)`	Ignores NULLs – counts only non-NULL values	`COUNT(nullable_col)` may be < `COUNT(*)`
`SUM(column)`	Ignores NULLs – sums non-NULL values	`SUM(10, 20, NULL)` = 30, not NULL
`AVG(column)`	Ignores NULLs – averages non-NULL values	`AVG(10, 20, NULL)` = 15, not 10
`MIN/MAX`	Ignores NULLs – finds min/max of non-NULLs	`MAX(10, 5, NULL)` = 10
`SUM/AVG` (all NULL)	Returns NULL if all values are NULL	`SUM(NULL, NULL)` = NULL, not 0

The Average Surprise

AVG ignores NULLs, which may not match business expectations. If you have scores [100, 90, NULL, 80], AVG = 90 (270/3), not 67.5 (270/4). If NULL means 'absent' = 0, you need AVG(COALESCE(score, 0)). Know your business requirements!

null-in-aggregates.sql
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
-- Test data
CREATE TABLE scores (
    student_id  INT,
    subject     VARCHAR(50),
    score       INT
);
 
INSERT INTO scores VALUES
    (1, 'Math', 100),
    (1, 'Science', 90),
    (1, 'History', NULL),   -- Absent / not taken
    (2, 'Math', 80),
    (2, 'Science', NULL),
    (2, 'History', 70);
 
-- COUNT behavior
SELECT 
    COUNT(*) AS total_rows,              -- 6 (all rows)
    COUNT(score) AS non_null_scores,     -- 4 (excludes 2 NULLs)
    COUNT(student_id) AS student_count   -- 6 (no NULLs in student_id)
FROM scores;
 
-- SUM and AVG ignore NULLs
SELECT 
    SUM(score) AS total,          -- 340 (100+90+80+70)
    AVG(score) AS average,        -- 85 (340/4, not 340/6!)
    COUNT(*) AS row_count,        -- 6
    COUNT(score) AS score_count   -- 4
FROM scores;
 
-- Per-student averages (NULLs excluded automatically)
SELECT 
    student_id,
    AVG(score) AS avg_score,           -- Student 1: 95 (190/2), Student 2: 75 (150/2)
    COUNT(*) AS subjects_enrolled,      -- 3 each
    COUNT(score) AS subjects_scored     -- 2 each
FROM scores
GROUP BY student_id;
 
-- What if NULL should count as zero?
SELECT 
    student_id,
    AVG(COALESCE(score, 0)) AS avg_with_zeros,  -- Treats NULL as 0
    AVG(score) AS avg_ignoring_nulls            -- Standard behavior
FROM scores
GROUP BY student_id;
-- Student 1: avg_with_zeros = 63.3 (190/3), avg_ignoring_nulls = 95 (190/2)
 
-- All NULLs: aggregate returns NULL
SELECT 
    SUM(score) AS sum_nulls,    -- NULL (not 0!)
    AVG(score) AS avg_nulls,    -- NULL
    MAX(score) AS max_nulls     -- NULL
FROM scores
WHERE score IS NULL;
 
-- Handle all-NULL case with COALESCE
SELECT COALESCE(SUM(score), 0) AS safe_sum
FROM scores
WHERE score IS NULL;  -- Returns 0 instead of NULL
 
-- GROUP BY treats NULL as a groupable value
INSERT INTO scores VALUES (3, NULL, 50);  -- NULL subject
 
SELECT subject, SUM(score)
FROM scores
GROUP BY subject;
-- Returns: Math:180, Science:90, History:70, NULL:50
-- NULL is its own group!

Key Implications:

COUNT(*) vs COUNT(column) — Always know which you need. They can differ dramatically.
AVG divisor — AVG divides by non-NULL count, not total rows. This can drastically change results.
SUM of no rows — Returns NULL, not 0. Wrap in COALESCE if you need 0.
GROUP BY NULL — NULL is grouped as its own category, which may or may not be desired.
DISTINCT with NULL — Multiple NULLs collapse to one NULL in DISTINCT/GROUP BY (NULLs are considered equal for grouping, even though NULL ≠ NULL in comparisons).

NULL in Sorting and Indexing

NULL values have specific behavior in sorting and indexing that varies between database systems. Understanding these behaviors is essential for predictable query results and efficient access paths.

NULL Sorting Order by Database
Database	ASC Order	DESC Order	Control Option
PostgreSQL	NULLs LAST	NULLs FIRST	`NULLS FIRST` / `NULLS LAST`
MySQL	NULLs FIRST	NULLs LAST	No direct syntax; use expression
Oracle	NULLs LAST	NULLs FIRST	`NULLS FIRST` / `NULLS LAST`
SQL Server	NULLs FIRST	NULLs FIRST	Use `CASE` expression
SQLite	NULLs FIRST	NULLs LAST	`NULLS FIRST` / `NULLS LAST` (3.30+)

null-sorting-indexing.sql
SQL (PostgreSQL)
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
-- Test data
CREATE TABLE product (
    product_id      SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    price           DECIMAL(10,2),         -- Nullable
    category_id     INT                    -- Nullable
);
 
INSERT INTO product (name, price, category_id) VALUES
    ('Widget A', 19.99, 1),
    ('Widget B', NULL, 1),
    ('Widget C', 29.99, NULL),
    ('Widget D', NULL, NULL);
 
-- Default sorting (PostgreSQL: NULLs last in ASC)
SELECT * FROM product ORDER BY price ASC;
-- Widget A: 19.99
-- Widget C: 29.99
-- Widget B: NULL
-- Widget D: NULL
 
-- Explicit NULL position (PostgreSQL, Oracle)
SELECT * FROM product ORDER BY price ASC NULLS FIRST;
-- Widget B: NULL
-- Widget D: NULL
-- Widget A: 19.99
-- Widget C: 29.99
 
SELECT * FROM product ORDER BY price DESC NULLS LAST;
-- Widget C: 29.99
-- Widget A: 19.99
-- Widget B: NULL
-- Widget D: NULL
 
-- Portable NULL-first/last using CASE
SELECT * FROM product 
ORDER BY 
    CASE WHEN price IS NULL THEN 0 ELSE 1 END,  -- NULLs first
    price ASC;
 
SELECT * FROM product 
ORDER BY 
    CASE WHEN price IS NULL THEN 1 ELSE 0 END,  -- NULLs last
    price ASC;
 
-- INDEXING with NULLs
 
-- Standard B-tree indexes CAN include NULL values
CREATE INDEX idx_product_price ON product(price);
 
-- Query can use index for NULL check
EXPLAIN SELECT * FROM product WHERE price IS NULL;
-- May use idx_product_price with NULL scan
 
-- Note: Some databases (older Oracle) don't store NULL-only entries
-- in single-column indexes. Multi-column indexes or partial indexes help.
 
-- Partial index: Exclude NULLs
CREATE INDEX idx_product_price_not_null 
    ON product(price) WHERE price IS NOT NULL;
 
-- Partial index: Only NULLs
CREATE INDEX idx_product_price_null 
    ON product(product_id) WHERE price IS NULL;
 
-- UNIQUE constraints and NULL
-- Most databases: Multiple NULLs allowed in UNIQUE column
-- (NULL is not equal to NULL, so no uniqueness violation)
 
ALTER TABLE product ADD CONSTRAINT uq_sku UNIQUE (price);
-- This allows multiple rows with price = NULL
 
-- PostgreSQL specific: Exclude NULLs from unique constraint
CREATE UNIQUE INDEX idx_unique_price_not_null 
    ON product(price) WHERE price IS NOT NULL;

UNIQUE Constraints and NULL

In most SQL databases, NULL values are NOT considered duplicates for UNIQUE constraints—you can have many rows with NULL in a UNIQUE column. This follows from NULL ≠ NULL logic. If you need unique non-NULL values with at-most-one NULL, use a partial unique index or check constraint.

NULL Handling Strategies and Best Practices

Effective NULL handling requires proactive strategies in schema design, query writing, and application logic. Here are battle-tested approaches:

NULL Handling Best Practices

•Minimize Nullable Columns — Default to NOT NULL. Only allow NULL when the absence of a value is semantically meaningful.
•Use COALESCE for Defaults — COALESCE(nullable_col, default_value) provides a non-NULL alternative in queries.
•Prefer NOT EXISTS over NOT IN — NOT EXISTS handles NULLs correctly; NOT IN with NULLs returns unexpected results.
•Explicit NULL Checks — Always use IS NULL / IS NOT NULL, never = NULL or <> NULL.
•Document NULL Semantics — Comment what NULL means for each nullable column: unknown, inapplicable, or withheld.
•Consider Default Values — Instead of NULL, use sentinel values like '' for strings, 0 for counts, or 'N/A' for codes—if semantically appropriate.
•Use Indicator Columns — When NULL is ambiguous, add explicit status columns like has_phone, phone_verified, etc.

null-strategies.sql
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
-- Strategy 1: COALESCE for display and calculation
SELECT 
    customer_id,
    COALESCE(phone_number, 'Not provided') AS phone_display,
    COALESCE(discount_rate, 0) AS effective_discount,
    price * (1 - COALESCE(discount_rate, 0)) AS final_price
FROM customer_orders;
 
-- Strategy 2: NOT EXISTS instead of NOT IN
-- BAD: NOT IN with potential NULLs
SELECT * FROM products
WHERE product_id NOT IN (
    SELECT product_id FROM discontinued  -- Fails if any NULL exists
);
 
-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued d 
    WHERE d.product_id = p.product_id
);
 
-- Strategy 3: NULLIF to create NULLs intentionally
-- Replace empty strings with NULL for consistent handling
UPDATE customers
SET middle_name = NULLIF(TRIM(middle_name), '');
 
-- Avoid division by zero
SELECT 
    total / NULLIF(count, 0) AS safe_average
FROM summaries;
 
-- Strategy 4: NULL-safe equality (database-specific)
-- PostgreSQL: IS NOT DISTINCT FROM
SELECT * FROM t1, t2 
WHERE t1.nullable_col IS NOT DISTINCT FROM t2.nullable_col;
-- This returns TRUE for (NULL, NULL), unlike =
 
-- MySQL: <=> (NULL-safe equal)
-- SELECT * FROM t1, t2 WHERE t1.nullable_col <=> t2.nullable_col;
 
-- Strategy 5: Design patterns to avoid NULL
 
-- Pattern A: Default values instead of NULL
CREATE TABLE user_preferences (
    user_id             INT PRIMARY KEY,
    theme               VARCHAR(20) NOT NULL DEFAULT 'light',
    notifications       BOOLEAN NOT NULL DEFAULT true,
    language_code       CHAR(2) NOT NULL DEFAULT 'en'
);
 
-- Pattern B: Separate table for optional data
CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL
);
 
CREATE TABLE employee_phone (
    employee_id     INT PRIMARY KEY REFERENCES employee(employee_id),
    phone_number    VARCHAR(20) NOT NULL,
    phone_type      VARCHAR(20) NOT NULL
);
-- No phone = no row in employee_phone (instead of NULL)
 
-- Pattern C: Status columns for clarity
CREATE TABLE order_shipment (
    order_id            INT PRIMARY KEY,
    shipping_status     VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (shipping_status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    shipped_at          TIMESTAMP,  -- NULL if not shipped yet
    delivered_at        TIMESTAMP   -- NULL if not delivered yet
    
    -- Constraints enforce consistency
    CONSTRAINT ship_date_required 
        CHECK (shipping_status IN ('pending', 'cancelled') OR shipped_at IS NOT NULL),
    CONSTRAINT deliver_date_logic
        CHECK (delivered_at IS NULL OR shipped_at IS NOT NULL)
);

The NOT NULL Default Philosophy

Start every column as NOT NULL and justify each exception. Ask: 'What does NULL mean here? Is there a better representation?' Often, a default value, a separate table, or a status column provides clearer semantics than NULL.

Summary: Mastering NULL Values

NULL is one of the most important and most misunderstood aspects of the relational model. Mastering NULL behavior is essential for writing correct queries and designing robust schemas. Let's consolidate the key concepts:

Key Takeaways

•NULL is not a value—it's a marker — NULL represents absence of value, not zero, empty string, or false.
•NULL introduces three-valued logic — Expressions evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN behaves like FALSE in WHERE clauses.
•NULL ≠ NULL — You cannot compare NULLs with = or <>. Use IS NULL and IS NOT NULL exclusively.
•NULL propagates through expressions — Arithmetic, string operations, and comparisons with NULL generally yield NULL or UNKNOWN.
•Aggregates ignore NULL — SUM, AVG, MIN, MAX skip NULLs. COUNT(*) includes all rows, COUNT(column) excludes NULLs.
•NOT IN with NULL is dangerous — If a subquery can return NULL, NOT IN may return no rows. Use NOT EXISTS instead.
•Minimize nullable columns — Default to NOT NULL; allow NULL only when absence is semantically meaningful. Consider defaults, separate tables, or status columns.

What's Next:

With NULL values understood, we complete our exploration of attributes and domains by examining domain constraints—the mechanisms that enforce data validity beyond simple type checking, ensuring that only meaningful values can enter the database.

Page Complete

You now have deep understanding of NULL—its meaning, its quirky three-valued logic, its behavior in operations and aggregates, and strategies for handling it effectively. This knowledge will help you avoid subtle bugs and design cleaner schemas that minimize NULL-related complexity.