How NULL Values Are Represented in SQLite: A Complete Guide

If there’s one concept in SQL that trips up beginners more than almost anything else, it’s NULL. I remember the first time I ran a query expecting certain rows to show up and they simply vanished — the culprit, every single time, turned out to be a misunderstanding of how NULL actually behaves. In this article, I want to walk through exactly how SQLite represents and handles NULL values, the syntax you need to work with them correctly, and the practical gotchas that catch almost everyone at some point.

What Does NULL Actually Mean?

NULL in SQLite — and in SQL generally — represents the absence of a value. It is not the same as zero, it is not the same as an empty string, and it is not the same as false. NULL means “unknown” or “not applicable” or “missing.” When a column contains NULL, it means the database genuinely does not have a value stored there, as opposed to having a value that happens to be blank or zero.

This distinction matters enormously. If I have a phone_number column and it’s NULL, that means we don’t know the person’s phone number. If it were an empty string '' instead, that would technically mean the phone number is known to be “nothing” — which is a different, and honestly less useful, semantic meaning in most real-world contexts. Understanding this difference is the foundation for understanding everything else about NULL.

SQLite’s Storage Classes and NULL

SQLite uses a dynamic type system with what it calls “storage classes” rather than rigid, fixed column types like many other database engines. The five storage classes in SQLite are:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

NULL is literally one of SQLite’s fundamental storage classes, not a special flag bolted onto another type. This means any column, regardless of its declared type — whether it’s declared as INTEGER, TEXT, REAL, or anything else — can store a NULL value unless that column has been explicitly constrained with NOT NULL. Internally, when SQLite stores a NULL, it doesn’t store a “blank integer” or a “blank string” — it stores a distinct marker indicating that no value is present at all.

Declaring Columns to Allow or Disallow NULL

By default, columns in SQLite allow NULL values unless you specify otherwise.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    phone_number TEXT,
    referred_by INTEGER
);

In this example, name is required — attempting to insert a row without a name (or explicitly inserting NULL for name) will cause SQLite to reject the operation with a constraint violation. Meanwhile, phone_number and referred_by are optional; SQLite will happily store NULL in either of them if no value is provided.

INSERT INTO customers (id, name) VALUES (1, 'Sana Malik');

This works fine even though phone_number and referred_by weren’t provided — they simply become NULL.

INSERT INTO customers (id, name, phone_number) VALUES (2, NULL, '0300-1234567');

This fails, because name has a NOT NULL constraint and we explicitly tried to insert NULL into it.

Inserting NULL Explicitly

You can explicitly insert a NULL value using the NULL keyword:

INSERT INTO customers (id, name, phone_number, referred_by)
VALUES (3, 'Ali Raza', NULL, NULL);

You can also omit a column entirely from your INSERT statement’s column list, and if that column has no DEFAULT value specified, it will default to NULL automatically.

Checking for NULL: IS NULL and IS NOT NULL

This is where many beginners stumble. You might expect this to work:

SELECT * FROM customers WHERE phone_number = NULL;

It doesn’t. This query returns zero rows every single time, no matter what data you have. The reason is that NULL represents an unknown value, and in SQL’s three-valued logic (TRUE, FALSE, and UNKNOWN), comparing anything to an unknown value — including another NULL — always produces UNKNOWN, not TRUE. Since WHERE clauses only keep rows where the condition evaluates to TRUE, rows with a NULL comparison are always excluded, even when comparing NULL = NULL.

Instead, you must use the special IS NULL and IS NOT NULL operators:

SELECT * FROM customers WHERE phone_number IS NULL;

SELECT * FROM customers WHERE phone_number IS NOT NULL;

These operators are specifically designed to test for the presence or absence of NULL, and they behave correctly where the = and != operators cannot.

SQLite also supports the shorthand ISNULL and NOTNULL as postfix operators:

SELECT * FROM customers WHERE phone_number ISNULL;
SELECT * FROM customers WHERE phone_number NOTNULL;

These are functionally identical to IS NULL and IS NOT NULL respectively, though IS NULL and IS NOT NULL are more standard and more widely recognized, so I generally recommend sticking with those for readability, especially if others will maintain your SQL later.

NULL in Comparisons and Three-Valued Logic

SQL uses three-valued logic rather than simple boolean true/false logic. Any expression involving NULL in a comparison evaluates to UNKNOWN rather than TRUE or FALSE. This has ripple effects throughout your queries.

SELECT 1 = NULL;      -- returns NULL (not 0, not 1)
SELECT NULL = NULL;   -- returns NULL
SELECT 1 <> NULL;     -- returns NULL
SELECT NULL IS NULL;  -- returns 1 (true)

This behavior extends to AND, OR, and NOT as well:

SELECT NULL AND 1;   -- NULL
SELECT NULL AND 0;   -- 0 (because the result is false regardless of the unknown operand)
SELECT NULL OR 1;    -- 1 (because the result is true regardless of the unknown operand)
SELECT NULL OR 0;    -- NULL
SELECT NOT NULL;     -- NULL

Notice how NULL AND 0 correctly returns 0, and NULL OR 1 correctly returns 1 — because in both cases, the known operand alone is sufficient to determine the outcome regardless of what the unknown value actually is. This is a subtle but logically consistent aspect of three-valued logic, and it’s worth internalizing because it explains a lot of “unexpected” query results.

NULL in WHERE Clauses With NOT IN

Here’s a classic trap that has caught me off guard more than once. Consider:

SELECT * FROM products
WHERE category_id NOT IN (SELECT category_id FROM archived_categories);

If the subquery SELECT category_id FROM archived_categories returns even a single NULL value among its results, the entire NOT IN clause will return zero rows for every single row in products — even rows that clearly don’t match any of the non-null values in the list. This is because NOT IN is internally evaluated as a series of <> comparisons combined with AND, and as soon as one of those comparisons hits a NULL, the overall result becomes UNKNOWN rather than TRUE.

The safe fix is to filter out NULL explicitly in the subquery:

SELECT * FROM products
WHERE category_id NOT IN (
    SELECT category_id FROM archived_categories WHERE category_id IS NOT NULL
);

I cannot stress enough how important it is to remember this pattern. It’s one of the most common silent bugs in SQL code, because the query runs without any error — it just quietly returns the wrong (usually empty) result set.

NULL and Aggregate Functions

Most aggregate functions in SQLite ignore NULL values rather than treating them as zero or including them in a way that skews the result.

SELECT AVG(salary) FROM employees;

If some rows have a NULL salary, those rows are excluded from both the sum and the count used to calculate the average — the average is computed only from the rows that actually have a salary value. This is usually the behavior you want, but it’s important to be conscious of it, especially when comparing COUNT(*) (which counts all rows, including those with NULL in any column) against COUNT(column_name) (which counts only rows where that specific column is not NULL).

SELECT COUNT(*) AS total_rows, COUNT(phone_number) AS rows_with_phone
FROM customers;

The difference between these two counts tells you exactly how many rows have a missing phone number, which is a handy pattern for quickly auditing data completeness.

Using COALESCE and IFNULL to Handle NULL

SQLite provides two very useful functions for substituting a default value in place of NULL.

IFNULL(expr1, expr2) returns expr1 if it is not NULL, otherwise it returns expr2.

SELECT name, IFNULL(phone_number, 'Not Provided') AS phone_display
FROM customers;

COALESCE(expr1, expr2, ..., exprN) is a more general-purpose version that accepts any number of arguments and returns the first one that is not NULL.

SELECT COALESCE(mobile_number, home_number, work_number, 'No Contact Available') AS best_contact
FROM customers;

I use COALESCE far more often than IFNULL in real projects because it’s more flexible — you often have several possible fallback values rather than just one, and COALESCE handles that cleanly in a single expression.

Sorting With NULL Values

By default, in SQLite, NULL values are treated as smaller than any other value when sorting. That means in an ascending ORDER BY, NULL values appear first; in a descending ORDER BY, they appear last.

SELECT * FROM customers ORDER BY phone_number ASC;
-- rows with NULL phone_number appear first

SELECT * FROM customers ORDER BY phone_number DESC;
-- rows with NULL phone_number appear last

If you want more control over where NULL values land in your sorted results, SQLite (from version 3.30.0 onward) supports the NULLS FIRST and NULLS LAST modifiers directly:

SELECT * FROM customers ORDER BY phone_number DESC NULLS LAST;

This gives you explicit control rather than relying on the default behavior, which I recommend whenever the position of NULL rows genuinely matters to how the results will be interpreted or displayed.

NULL and UNIQUE Constraints

Here’s a nuance that surprises a lot of people: NULL is treated as a distinct, non-equal value with respect to UNIQUE constraints. This means you can insert multiple rows with NULL in a column that has a UNIQUE constraint, and SQLite will not consider those NULL values as duplicates of each other.

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

INSERT INTO contacts (id, email) VALUES (1, NULL);
INSERT INTO contacts (id, email) VALUES (2, NULL);

Both of these inserts succeed, even though email has a UNIQUE constraint, because SQLite (correctly, per the SQL standard) does not consider two unknown values to be duplicates of each other. This is logical once you think about it — if you don’t know two people’s emails, you can’t actually claim their emails are the same.

NULL in CASE Expressions

NULL interacts with CASE expressions in a predictable but occasionally tricky way. Remember, a bare comparison to NULL never evaluates to true, so you need IS NULL inside your CASE logic if you want to catch NULL values explicitly.

SELECT name,
    CASE
        WHEN phone_number IS NULL THEN 'Missing'
        WHEN phone_number = '' THEN 'Empty'
        ELSE phone_number
    END AS phone_status
FROM customers;

Best Practices for Working With NULL

1. Always use IS NULL / IS NOT NULL, never = NULL or != NULL.
2. Be careful with NOT IN and subqueries that might return NULL — filter those out explicitly.
3. Use NOT NULL constraints deliberately on columns that should always have a meaningful value, like primary identifying fields.
4. Don’t confuse NULL with an empty string or zero. They represent fundamentally different concepts — “unknown” versus “known to be blank or zero.”
5. Use COALESCE or IFNULL to provide sensible defaults for display or calculation purposes rather than letting NULL propagate unexpectedly through your application.
6. Remember NULL‘s special behavior with UNIQUE constraints when designing schemas that rely on uniqueness for optional fields.
7. Test your queries with real NULL data, not just fully populated sample rows, since NULL edge cases are exactly where subtle bugs tend to hide.

NULL and Foreign Keys

NULL also plays a distinct role in foreign key relationships. If a foreign key column allows NULL (meaning it doesn’t have a NOT NULL constraint), you can insert a row with NULL in that column even while foreign key enforcement is active, and SQLite will not attempt to validate it against the referenced table. This makes logical sense — a NULL foreign key represents “this row has no associated parent record,” rather than “this row references a specific but invalid parent record.”

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    salesperson_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (salesperson_id) REFERENCES employees(id)
);

INSERT INTO orders (customer_id, salesperson_id) VALUES (5, NULL);

This insert succeeds even with foreign key enforcement turned on, because a NULL salesperson_id simply means “no salesperson assigned to this order yet,” not an invalid reference. If you want to require that every order genuinely has a salesperson, you’d add a NOT NULL constraint to that column specifically.

NULL Handling in String Concatenation

Another place NULL frequently surprises people is string concatenation using the || operator. If any operand in a concatenation expression is NULL, the entire result becomes NULL, not just the missing piece.

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM users;

If middle_name is NULL for a given row, the entire full_name result for that row comes back as NULL — not "John Smith" with an extra space, but a complete NULL. This catches a lot of people off guard when building display names, addresses, or any other concatenated string from multiple source columns where some might be missing.

The fix is to wrap potentially-NULL columns with COALESCE, converting them to empty strings (or another sensible default) before concatenating:

SELECT first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name AS full_name
FROM users;

NULL in GROUP BY

NULL values are grouped together as a single group when used in a GROUP BY clause, since SQLite treats all NULL values as belonging to the same group for grouping purposes (even though, as we covered earlier, NULL values are not considered “equal” to each other in most other contexts, like UNIQUE constraints or direct comparisons). This is a specific, standard exception worth remembering.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

If some employees have a NULL department (perhaps newly hired and not yet assigned), all of those employees will be grouped together under a single NULL department group in the results, rather than each forming their own separate group or being excluded entirely.

Frequently Asked Questions

Does an empty string count as NULL in SQLite?

No. An empty string ('') is a legitimate, known value of zero length — it’s fundamentally different from NULL, which represents the complete absence of a value. WHERE column = '' and WHERE column IS NULL will typically match entirely different sets of rows.

How do I count NULL and non-NULL values separately?

Use COUNT(*) for the total row count (including rows with NULL in any column), and COUNT(column_name) for a count that excludes NULL values specifically in that column. Subtracting one from the other tells you how many NULLs exist in that column.

Can a PRIMARY KEY column contain NULL?

In most cases, no — SQLite’s PRIMARY KEY implicitly behaves like NOT NULL in practice for INTEGER PRIMARY KEY columns tied to the rowid. However, there are some edge cases with non-integer or composite primary keys where this isn’t automatically enforced unless you add an explicit NOT NULL constraint, so it’s good practice to always add NOT NULL explicitly to primary key columns for clarity and safety.

Does ORDER BY treat NULL consistently across database systems?

Not necessarily — different database engines have different default behaviors for where NULL sorts. SQLite treats NULL as the smallest possible value by default, but if you’re porting SQL from another database system, always double-check this assumption, and use NULLS FIRST / NULLS LAST explicitly if the exact ordering matters for your application.

Wrapping Up

NULL is one of those concepts that seems simple on the surface — “it just means no value, right?” — but has surprisingly deep implications throughout SQLite’s comparison logic, aggregate functions, sorting behavior, and constraint handling. Once you internalize that NULL represents “unknown” rather than “blank” or “zero,” and that comparisons involving NULL follow three-valued logic rather than simple true/false logic, a huge number of confusing query results suddenly make perfect sense.

My honest advice: whenever a query returns fewer rows than you expect, or an aggregate looks off, check your data for NULL values first. Nine times out of ten, that’s exactly where the answer is hiding.