SQLite NULL Handling

NULL represents a missing or unknown value. It is one of the most misunderstood concepts in SQL. NULL is not zero, not an empty string, and not false. Understanding how SQLite handles NULL prevents silent bugs in queries and calculations.

What NULL Means in the Real World

COLUMN        VALUE    MEANING
────────────────────────────────────────────────────
phone         NULL     Phone number is not known
phone         ''       Phone number exists but is blank
discount      NULL     Discount hasn't been set yet
discount      0        Discount is set to zero
shipped_at    NULL     Order has not shipped yet
shipped_at    '2024-07-24'  Order shipped on this date

Sample Table

students
───────────────────────────────────────────────
id │ name    │ grade │ phone      │ scholarship
───┼─────────┼───────┼────────────┼────────────
1  │ Alice   │ A     │ 555-0101   │ 500
2  │ Bob     │ NULL  │ 555-0102   │ NULL
3  │ Carol   │ B     │ NULL       │ 300
4  │ Dan     │ NULL  │ NULL       │ NULL
5  │ Eve     │ A     │ 555-0105   │ 200

IS NULL and IS NOT NULL

-- Find students with no grade recorded
SELECT name FROM students WHERE grade IS NULL;

name
──────
Bob
Dan

-- Find students with a phone number
SELECT name FROM students WHERE phone IS NOT NULL;

name
──────
Alice
Bob
Eve

-- WRONG way to find NULLs (returns nothing):
SELECT name FROM students WHERE grade = NULL;

NULL in Arithmetic

Any arithmetic operation involving NULL produces NULL. Think of NULL as an unknown number — unknown + anything = still unknown.

SELECT 100 + NULL;     -- NULL
SELECT NULL * 5;       -- NULL
SELECT NULL / 2;       -- NULL

-- Practical impact on scholarship totals:
SELECT name, scholarship + 100 AS adjusted FROM students;

name  │ adjusted
──────┼──────────
Alice │ 600
Bob   │ NULL      ← NULL + 100 = NULL (not 100!)
Carol │ 400
Dan   │ NULL
Eve   │ 300

NULL in Comparisons

SELECT NULL = NULL;    -- NULL (not TRUE!)
SELECT NULL != NULL;   -- NULL
SELECT NULL > 5;       -- NULL
SELECT NULL = 0;       -- NULL

-- Rule: any comparison with NULL returns NULL (unknown)
-- NULL is not equal to anything, not even itself.

NULL in Aggregate Functions

Aggregate functions like COUNT, SUM, and AVG ignore NULL values automatically.

SELECT COUNT(scholarship) FROM students;
-- Result: 3   (Bob and Dan's NULLs are skipped)

SELECT SUM(scholarship) FROM students;
-- Result: 1000   (500 + 300 + 200; NULLs skipped)

SELECT AVG(scholarship) FROM students;
-- Result: 333.33   (1000 / 3 students with values)

-- But COUNT(*) counts rows, including those with NULLs:
SELECT COUNT(*) FROM students;
-- Result: 5   (all rows)

COALESCE — Replace NULL with a Default

COALESCE returns the first non-NULL value from a list of expressions. Use it to substitute a meaningful value wherever NULL would appear.

SELECT name,
       COALESCE(scholarship, 0) AS scholarship_amount
FROM students;

name  │ scholarship_amount
──────┼────────────────────
Alice │ 500
Bob   │ 0     ← NULL replaced with 0
Carol │ 300
Dan   │ 0     ← NULL replaced with 0
Eve   │ 200

-- Multiple fallbacks:
SELECT COALESCE(phone, email, 'No contact') AS contact
FROM students;
-- Returns phone if not NULL, else email, else 'No contact'

IFNULL — Two-Argument Shorthand

-- IFNULL(expression, fallback)
SELECT name, IFNULL(grade, 'Ungraded') AS grade FROM students;

name  │ grade
──────┼─────────
Alice │ A
Bob   │ Ungraded
Carol │ B
Dan   │ Ungraded
Eve   │ A

NULLIF — Return NULL When Two Values Match

NULLIF returns NULL if both arguments are equal. Otherwise it returns the first argument. This prevents division-by-zero errors.

-- Avoid division by zero
SELECT 100 / NULLIF(denominator, 0) FROM calculations;

-- If denominator = 0: NULLIF returns NULL → 100/NULL = NULL (safe)
-- If denominator = 5: NULLIF returns 5   → 100/5 = 20

NULL in ORDER BY

SELECT name, scholarship FROM students ORDER BY scholarship ASC;

name  │ scholarship
──────┼────────────
Bob   │ NULL    ← NULLs first in ASC
Dan   │ NULL
Eve   │ 200
Carol │ 300
Alice │ 500

ORDER BY scholarship DESC:
name  │ scholarship
──────┼────────────
Alice │ 500
Carol │ 300
Eve   │ 200
Bob   │ NULL    ← NULLs last in DESC
Dan   │ NULL

NULL in UNIQUE Columns

Multiple NULL values are allowed in a UNIQUE column. SQLite treats NULL as unknown — two unknowns are not considered equal, so no uniqueness violation occurs.

CREATE TABLE contacts (id INTEGER, email TEXT UNIQUE);

INSERT INTO contacts VALUES (1, 'a@x.com');
INSERT INTO contacts VALUES (2, NULL);      -- OK
INSERT INTO contacts VALUES (3, NULL);      -- OK (NULLs don't conflict)
INSERT INTO contacts VALUES (4, 'a@x.com'); -- ERROR: duplicate email

Key Takeaways

• NULL means unknown — not zero, not empty string, not false
• Use IS NULL and IS NOT NULL to check for NULL; = NULL never works
• Any arithmetic with NULL produces NULL
• Aggregate functions skip NULL values; COUNT(*) is the exception
• Use COALESCE() or IFNULL() to substitute defaults for NULL values