SQL NULL Comparisons Returning Wrong Results: IS NULL vs = NULL Traps

You write a perfectly reasonable WHERE column = NULL filter, run the query, and get zero rows back — even though you can see NULL values sitting right there in the table. This is not a bug in your database engine. It is a deliberate design choice rooted in three-valued logic, and it catches experienced developers just as often as beginners.

Understanding how NULL actually behaves in SQL will save you from silent data errors that are far worse than an empty result set.

What you'll learn

• Why = NULL never returns true, not even when comparing NULL to NULL
• The correct predicates: IS NULL and IS NOT NULL
• How NULLs propagate through JOINs, aggregates, and CASE expressions
• Common gotchas with NOT IN and subqueries that include NULLs
• Practical patterns for writing NULL-safe queries

Prerequisites

You should be comfortable writing basic SELECT statements with WHERE clauses. The examples use standard SQL that runs on PostgreSQL, MySQL, and SQL Server with minimal changes. Any differences between dialects are called out inline.

What NULL Actually Means

NULL in SQL does not mean zero, empty string, or false. It means unknown. The database has no idea what value belongs in that cell. That distinction matters because any comparison involving an unknown produces another unknown — not true, not false, but a third state called UNKNOWN.

SQL filters rows into your result set only when the WHERE condition evaluates to TRUE. Rows that produce FALSE are excluded. Rows that produce UNKNOWN are also excluded. That last part is where the trap lives.

In SQL's three-valued logic, the possible outcomes of a boolean expression are TRUE, FALSE, and UNKNOWN. A WHERE clause only passes rows where the result is TRUE.

Why = NULL Always Fails

When you write column = NULL, you are asking the database to compare a value to something unknown. The result cannot be true or false — it is UNKNOWN. The row is filtered out.

This applies even when you compare NULL to itself:

-- Both of these return UNKNOWN, not TRUE
SELECT NULL = NULL;   -- result: NULL (UNKNOWN)
SELECT NULL != NULL;  -- result: NULL (UNKNOWN)

Run that in PostgreSQL and you will see a NULL come back, not a 1 or a 0. The database is telling you it genuinely does not know whether two unknown values are equal.

This is not a quirk — it is defined in the SQL standard. Every major relational database follows it. The implication is straightforward: you cannot use equality operators to test for NULL.

The Correct Syntax: IS NULL and IS NOT NULL

IS NULL and IS NOT NULL are special predicates that were built specifically to test for the absence of a value. They always return TRUE or FALSE, never UNKNOWN.

-- Find rows where the column has no value
SELECT *
FROM orders
WHERE shipped_date IS NULL;

-- Find rows where the column has any value
SELECT *
FROM orders
WHERE shipped_date IS NOT NULL;

These predicates work exactly as you would expect. They look at whether the storage slot contains a value at all, bypassing the three-valued logic problem entirely.

Fixing the common mistake

If you inherited a query that uses = NULL and it returns no rows, the fix is a straightforward substitution:

-- Wrong: returns zero rows every time
SELECT * FROM customers WHERE phone = NULL;

-- Correct
SELECT * FROM customers WHERE phone IS NULL;

Some databases, notably SQL Server with ANSI_NULLS OFF, will let = NULL behave like IS NULL. Do not rely on this. That setting is deprecated and will be removed in a future version. Write IS NULL everywhere.

NULLs in JOIN Conditions

JOIN conditions use the same three-valued logic. If you join two tables on a column that contains NULLs in both tables, those rows will not match — even though the values look the same to the human eye.

-- Neither row matches because NULL != NULL evaluates to UNKNOWN
SELECT a.id, b.id
FROM table_a a
JOIN table_b b ON a.ref_code = b.ref_code;
-- Rows where ref_code IS NULL on either side are dropped

If you genuinely need NULL-to-NULL matching in a JOIN, use a COALESCE or an explicit IS NOT DISTINCT FROM (supported in PostgreSQL and most modern databases):

-- PostgreSQL: NULL-safe equality
SELECT a.id, b.id
FROM table_a a
JOIN table_b b ON a.ref_code IS NOT DISTINCT FROM b.ref_code;

-- Portable alternative using COALESCE (substitute -1 with a value
-- that cannot appear legitimately in your data)
SELECT a.id, b.id
FROM table_a a
JOIN table_b b ON COALESCE(a.ref_code, -1) = COALESCE(b.ref_code, -1);

The IS NOT DISTINCT FROM approach is cleaner when it is available. The COALESCE sentinel approach works across dialects but requires you to pick a sentinel value that genuinely cannot appear in the column.

NULLs in Aggregate Functions

Aggregate functions like SUM, AVG, MIN, and MAX silently ignore NULL values. This is usually what you want, but it becomes a trap when you are counting.

CREATE TABLE survey (respondent_id INT, score INT);
-- Imagine 10 rows, 3 of which have score = NULL

SELECT
    COUNT(*)        AS total_rows,      -- 10
    COUNT(score)    AS non_null_scores,  -- 7
    AVG(score)      AS avg_score         -- average of 7 values, not 10
FROM survey;

COUNT(*) counts rows regardless of NULL. COUNT(column) counts only non-NULL values in that column. If your denominator is wrong, your averages and percentages will be wrong too.

When you need to count NULLs explicitly, combine COUNT(*) with a CASE expression or a filtered count:

SELECT
    COUNT(*) FILTER (WHERE score IS NULL)     AS null_count,
    COUNT(*) FILTER (WHERE score IS NOT NULL) AS filled_count
FROM survey;
-- FILTER syntax works in PostgreSQL; use SUM(CASE WHEN...) in other dialects

NULLs in CASE Expressions

A CASE expression that does not explicitly handle NULL will treat it as a non-matching condition and fall through to the ELSE branch (or return NULL if no ELSE exists).

SELECT
    order_id,
    CASE status
        WHEN 'shipped'  THEN 'On the way'
        WHEN 'pending'  THEN 'Processing'
        -- If status IS NULL, none of the WHEN clauses match
        -- and this expression returns NULL silently
    END AS status_label
FROM orders;

Handle NULL explicitly in your CASE by adding an ELSE or a dedicated WHEN branch:

SELECT
    order_id,
    CASE
        WHEN status IS NULL  THEN 'Unknown'
        WHEN status = 'shipped' THEN 'On the way'
        WHEN status = 'pending' THEN 'Processing'
        ELSE 'Other'
    END AS status_label
FROM orders;

The NOT IN Trap with NULLs

This is the most dangerous NULL trap in practical SQL work. If a subquery used with NOT IN returns even one NULL, the entire outer query returns zero rows. No warning, no error — just silence.

-- Imagine category_ids contains the values (1, 2, NULL)
SELECT *
FROM products
WHERE category_id NOT IN (SELECT category_id FROM excluded_categories);
-- Returns ZERO rows if any category_id in excluded_categories is NULL

The reason is three-valued logic again. NOT IN expands to a series of inequality checks joined by AND. Any comparison with NULL produces UNKNOWN. UNKNOWN chained through AND collapses the whole expression to UNKNOWN, so no row passes the filter.

The fix is to use NOT EXISTS instead, which is NULL-safe and also tends to perform better on large datasets:

SELECT p.*
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM excluded_categories ec
    WHERE ec.category_id = p.category_id
);

Alternatively, add a WHERE column IS NOT NULL inside the subquery to remove any NULLs before they poison the outer filter:

SELECT *
FROM products
WHERE category_id NOT IN (
    SELECT category_id
    FROM excluded_categories
    WHERE category_id IS NOT NULL  -- explicitly exclude NULLs
);

NULL-Safe Equality Across Dialects

Different databases offer different tools for comparing two columns where either might be NULL:

If you are writing cross-dialect SQL or using an ORM that targets multiple backends, the safest portable pattern is an explicit CASE comparison:

-- Portable NULL-safe equality check
WHERE (a.col = b.col) OR (a.col IS NULL AND b.col IS NULL)

Common Pitfalls Summary

• Using = NULL in a WHERE clause — always returns zero rows. Use IS NULL.
• Expecting NULL = NULL to be true — it evaluates to UNKNOWN. Use IS NOT DISTINCT FROM or an explicit CASE.
• Forgetting NULL in NOT IN subqueries — one NULL in the subquery wipes out all results. Use NOT EXISTS.
• Trusting AVG to account for NULLs — NULLs are silently excluded from the denominator. Verify your row count.
• Leaving CASE expressions without an ELSE — NULL inputs fall through and return NULL, which may look like valid data downstream.
• Relying on dialect-specific NULL behavior — ANSI_NULLS OFF in SQL Server, for instance, changes behavior. Always write standard-compliant SQL.

Wrapping Up

NULL comparisons are one of the few areas where SQL actively works against your intuitions. The fixes are small — swapping = NULL for IS NULL, replacing NOT IN with NOT EXISTS, adding explicit NULL branches to CASE expressions — but the impact on result correctness is large.

Here are concrete steps to take right now:

1. Search your existing query library for = NULL and != NULL patterns and replace them with IS NULL and IS NOT NULL.
2. Audit any NOT IN (SELECT ...) pattern to check whether the subquery column is nullable, and switch to NOT EXISTS where it is.
3. Add a COUNT(*) vs COUNT(column) sanity check to reports that use AVG or percentage calculations on nullable columns.
4. Explicitly handle NULL in every CASE expression that reads from a nullable column — never rely on implicit fall-through.
5. If your team is on PostgreSQL, start using IS NOT DISTINCT FROM for any join or comparison where both sides can be NULL.