How to Handle NULL Values in SQLite

Key Insights

NULL in SQLite represents the absence of a value and behaves fundamentally different from empty strings or zero—you must use IS NULL or IS NOT NULL operators, never equality comparisons
Columns allow NULL by default in SQLite, so always explicitly define NOT NULL constraints on columns that should never be empty to prevent data integrity issues
Use COALESCE() for multiple fallback values and IFNULL() for simple two-value scenarios, but understand that NULL propagates through arithmetic operations and will turn any calculation into NULL unless handled

Understanding NULL in SQLite

NULL in SQLite is not a value—it’s the explicit absence of a value. This distinction matters because NULL behaves completely differently from empty strings (''), zero (0), or false. A column containing NULL means “no data exists here,” while an empty string means “the data exists and it’s an empty string.”

SQLite implements three-valued logic: TRUE, FALSE, and NULL. When NULL enters any comparison or logical operation, it typically produces NULL as the result, not TRUE or FALSE. This trips up developers who expect NULL to behave like other values.

-- Demonstrating NULL vs. empty string vs. zero
CREATE TABLE demo (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    notes TEXT
);

INSERT INTO demo (id, name, age, notes) VALUES 
    (1, 'Alice', 30, 'Active user'),
    (2, '', 25, ''),           -- Empty strings
    (3, 'Bob', 0, NULL),       -- Zero and NULL
    (4, NULL, NULL, NULL);     -- All NULL

-- These queries return different results
SELECT * FROM demo WHERE name = '';        -- Returns row 2 only
SELECT * FROM demo WHERE name IS NULL;     -- Returns row 4 only
SELECT * FROM demo WHERE age = 0;          -- Returns row 3 only
SELECT * FROM demo WHERE age IS NULL;      -- Returns row 4 only

The key takeaway: NULL is not equal to anything, not even itself. NULL = NULL evaluates to NULL (unknown), not TRUE.

Creating Tables with NULL Constraints

By default, SQLite allows NULL in every column unless you explicitly forbid it with a NOT NULL constraint. This permissive default can lead to data quality issues if you’re not careful. Always be intentional about which columns can contain NULL.

Use NOT NULL for columns that are essential to your business logic. Primary keys automatically get NOT NULL behavior, but other critical fields need explicit constraints.

-- Table with explicit NULL handling
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,           -- Implicitly NOT NULL
    email TEXT NOT NULL UNIQUE,            -- Required field
    username TEXT NOT NULL,                -- Required field
    display_name TEXT,                     -- Optional, NULL allowed
    last_login INTEGER,                    -- Optional timestamp
    bio TEXT DEFAULT '',                   -- Optional but defaults to empty string
    age INTEGER CHECK (age IS NULL OR age >= 0)  -- NULL or valid number
);

-- This succeeds
INSERT INTO users (user_id, email, username) 
VALUES (1, 'alice@example.com', 'alice');

-- This fails: NOT NULL constraint violation
INSERT INTO users (user_id, email) 
VALUES (2, 'bob@example.com');

-- This succeeds: NULL is allowed in display_name
INSERT INTO users (user_id, email, username, display_name) 
VALUES (3, 'charlie@example.com', 'charlie', NULL);

Notice the CHECK constraint on age—this allows NULL but ensures any actual value is non-negative. This pattern is useful when you want optional fields that must meet certain criteria when present.

Checking for NULL Values

The most common mistake when working with NULL is using equality operators. You cannot use = or != to check for NULL. Always use IS NULL or IS NOT NULL.

-- Wrong approach (returns no rows even when NULLs exist)
SELECT * FROM users WHERE display_name = NULL;

-- Correct approach
SELECT * FROM users WHERE display_name IS NULL;

-- Finding users with display names
SELECT * FROM users WHERE display_name IS NOT NULL;

-- Combining with other conditions
SELECT * FROM users 
WHERE display_name IS NOT NULL 
  AND last_login > 1640000000;

-- Using COALESCE to provide defaults in queries
SELECT 
    username,
    COALESCE(display_name, username) AS shown_name,
    COALESCE(bio, 'No bio provided') AS bio_text
FROM users;

The COALESCE() function returns the first non-NULL argument, making it perfect for providing fallback values in queries without changing your actual data.

Handling NULL in Calculations and Comparisons

NULL is infectious in calculations—any arithmetic operation involving NULL produces NULL. This can silently break calculations if you’re not careful.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    base_price REAL NOT NULL,
    discount REAL,        -- NULL means no discount
    tax_rate REAL,        -- NULL means no tax
    shipping REAL
);

INSERT INTO orders VALUES 
    (1, 100.00, 10.00, 0.08, 5.00),
    (2, 50.00, NULL, 0.08, NULL),
    (3, 75.00, 5.00, NULL, 3.00);

-- This produces NULL for order 2 (discount is NULL)
SELECT 
    order_id,
    base_price - discount AS discounted_price
FROM orders;

-- Correct approach: handle NULL explicitly
SELECT 
    order_id,
    base_price - IFNULL(discount, 0) AS discounted_price,
    (base_price - IFNULL(discount, 0)) * (1 + IFNULL(tax_rate, 0)) 
        + IFNULL(shipping, 0) AS total
FROM orders;

-- NULL in sorting (appears first by default)
SELECT order_id, discount 
FROM orders 
ORDER BY discount;  -- NULL values appear first

-- Explicit control over NULL positioning
SELECT order_id, discount 
FROM orders 
ORDER BY discount IS NULL, discount;  -- NULLs last

String concatenation with NULL also produces NULL, which often surprises developers:

SELECT 
    username,
    'Hello, ' || display_name AS greeting  -- NULL if display_name is NULL
FROM users;

-- Better approach
SELECT 
    username,
    'Hello, ' || COALESCE(display_name, username) AS greeting
FROM users;

NULL-Safe Functions and Operators

SQLite provides three main functions for NULL handling: IFNULL(), COALESCE(), and NULLIF(). Each serves a specific purpose.

IFNULL(value, replacement) takes exactly two arguments and returns the first if it’s not NULL, otherwise the second. It’s the simplest and fastest option for basic NULL handling.

COALESCE(value1, value2, ..., valueN) accepts multiple arguments and returns the first non-NULL value. Use this when you have multiple potential fallbacks.

NULLIF(value1, value2) returns NULL if the two values are equal, otherwise returns the first value. This is useful for converting specific values to NULL.

-- IFNULL: simple two-value scenario
SELECT 
    username,
    IFNULL(display_name, username) AS name
FROM users;

-- COALESCE: multiple fallbacks
CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    mobile TEXT,
    home_phone TEXT,
    work_phone TEXT,
    email TEXT NOT NULL
);

SELECT 
    contact_id,
    COALESCE(mobile, home_phone, work_phone, 'No phone') AS primary_contact
FROM contacts;

-- NULLIF: converting specific values to NULL
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    stock_level INTEGER DEFAULT 0
);

-- Treat 0 stock as NULL for reporting
SELECT 
    name,
    NULLIF(stock_level, 0) AS actual_stock  -- Returns NULL when stock is 0
FROM products;

-- Practical use: calculating average of only positive values
SELECT AVG(NULLIF(stock_level, 0)) AS avg_positive_stock
FROM products;

Updating and Inserting NULL Values

Inserting and updating NULL values requires explicit syntax. You can’t use an empty space or omit the value entirely in most cases.

-- Explicit NULL insertion
INSERT INTO users (user_id, email, username, display_name)
VALUES (10, 'test@example.com', 'testuser', NULL);

-- Updating to NULL
UPDATE users 
SET last_login = NULL 
WHERE user_id = 10;

-- Conditional update: only set to NULL if condition met
UPDATE users 
SET display_name = NULL 
WHERE display_name = '' OR display_name = username;

-- Using CASE for conditional NULL assignment
UPDATE orders
SET discount = CASE 
    WHEN base_price < 25 THEN NULL  -- No discount for small orders
    WHEN base_price >= 100 THEN 15.00
    ELSE 5.00
END;

-- DEFAULT keyword (uses table's default value)
INSERT INTO users (user_id, email, username, bio)
VALUES (11, 'another@example.com', 'another', DEFAULT);

Best Practices and Common Pitfalls

Effective NULL handling requires a consistent strategy across your application. Here are the key principles:

1. Be explicit about NULL constraints. Don’t rely on defaults. If a column should never be NULL, add NOT NULL.

2. Prefer NOT NULL with defaults over allowing NULL. For optional text fields, consider using empty strings with DEFAULT '' rather than allowing NULL. This simplifies queries.

3. Always use IS NULL and IS NOT NULL. Never use = NULL or != NULL.

4. Handle NULL in calculations explicitly. Don’t let NULL silently break your math.

5. Be careful with indexes. NULL values are indexed in SQLite, but queries like WHERE column != 'value' won’t match NULL rows.

Here’s a before-and-after refactoring example:

-- BEFORE: Problematic schema and queries
CREATE TABLE invoices_bad (
    invoice_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    amount REAL,
    paid_date INTEGER,
    notes TEXT
);

-- This query has problems with NULL
SELECT 
    customer_name,
    amount * 1.08 AS total,  -- Breaks if amount is NULL
    paid_date - 1640000000 AS days_since_epoch  -- Breaks if paid_date is NULL
FROM invoices_bad
WHERE notes != '';  -- Doesn't match NULL notes

-- AFTER: Improved schema and queries
CREATE TABLE invoices_good (
    invoice_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    amount REAL NOT NULL CHECK (amount > 0),
    paid_date INTEGER,  -- NULL means unpaid
    notes TEXT DEFAULT ''
);

-- Better query with explicit NULL handling
SELECT 
    customer_name,
    amount * 1.08 AS total,
    CASE 
        WHEN paid_date IS NULL THEN 'Unpaid'
        ELSE 'Paid ' || (julianday('now') - julianday(paid_date, 'unixepoch')) || ' days ago'
    END AS payment_status,
    CASE 
        WHEN notes = '' THEN 'No notes'
        ELSE notes
    END AS notes_display
FROM invoices_good;

The improved version makes NULL handling explicit, uses constraints to prevent invalid data, and provides meaningful defaults. This approach reduces bugs and makes your queries more predictable.

Remember: NULL is a tool for representing truly missing information. Use it intentionally, handle it explicitly, and your SQLite applications will be more robust and maintainable.