SQL NULL Comparison: Why = NULL Never Works — sqlfmt

sqlfmt▍

SQL NULL Comparison: Why = NULL Never Works — sqlfmt

If you've ever written WHERE deleted_at = NULL and gotten zero rows back — even when you know nulls are in that column — you've hit one of SQL's most reliable trip wires. The = operator simply does not work with NULL. Not because of a bug, but because of how SQL defines NULL itself.

NULL Is Not a Value, It Is the Absence of One

NULL in SQL represents an unknown or missing value — not zero, not an empty string, not false. Because it is unknown, any arithmetic or comparison involving NULL produces another unknown. SQL calls this three-valued logic: every expression can evaluate to TRUE, FALSE, or UNKNOWN. A WHERE clause only passes rows where the condition is TRUE. UNKNOWN is treated like FALSE — the row is dropped.

Three-valued logic — every comparison with NULL returns UNKNOWN

-- These all return UNKNOWN (not FALSE)
SELECT NULL = NULL;      -- UNKNOWN
SELECT NULL = 1;         -- UNKNOWN
SELECT NULL != NULL;     -- UNKNOWN
SELECT NULL > 0;         -- UNKNOWN
SELECT 1 + NULL;         -- NULL

-- IS NULL / IS NOT NULL are the only operators that return TRUE or FALSE
SELECT NULL IS NULL;     -- TRUE
SELECT NULL IS NOT NULL; -- FALSE

Why WHERE column = NULL Returns Zero Rows

The WHERE clause keeps a row only when the condition evaluates to TRUE. When you write column = NULL, the result is always UNKNOWN — regardless of whether the column actually contains a NULL value. So the condition is never TRUE, and no rows pass the filter. The same applies to column != NULL and column <> NULL.

= NULL silently returns nothing — IS NULL returns the right rows

-- Table: users
-- id | name    | deleted_at
--  1 | Alice   | 2024-01-15
--  2 | Bob     | NULL
--  3 | Charlie | NULL

-- Returns 0 rows — = NULL is always UNKNOWN
SELECT *
FROM users
WHERE deleted_at = NULL;

-- Returns 2 rows (Bob and Charlie)
SELECT *
FROM users
WHERE deleted_at IS NULL;

The Correct Operators: IS NULL and IS NOT NULL

SQL provides two dedicated predicates for NULL checks. IS NULL returns TRUE when the value is NULL. IS NOT NULL returns TRUE when the value is anything other than NULL. These work identically in PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

IS NULL and IS NOT NULL in practice

-- Find orders that haven't shipped yet
SELECT order_id, customer_id, created_at
FROM orders
WHERE shipped_at IS NULL;

-- Find orders that have shipped
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL;

-- Avoid double negation — this is harder to read than IS NULL
SELECT order_id
FROM orders
WHERE NOT shipped_at IS NOT NULL;

Replacing NULL with a Default: COALESCE and NULLIF

Use COALESCE when you need a fallback value in place of NULL — it returns the first non-NULL argument from its list. NULLIF does the inverse: it converts a specific value into NULL, which is useful to avoid divide-by-zero errors or normalize sentinel values.

COALESCE and NULLIF

-- COALESCE: return the first non-NULL value
SELECT
  user_id,
  COALESCE(display_name, email, 'Anonymous') AS name
FROM users;

-- NULLIF: return NULL when the two arguments are equal (avoids divide-by-zero)
SELECT
  product_id,
  revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;

-- Chain them: turn empty strings into NULL, then apply a default
SELECT
  COALESCE(NULLIF(TRIM(phone), ''), 'N/A') AS phone
FROM contacts;

How NULL Affects Aggregate Functions

Aggregate functions — SUM, AVG, MIN, MAX — silently skip NULL values. COUNT has a critical distinction: COUNT(*) counts all rows including those where the target column is NULL, while COUNT(column) counts only non-NULL values. This difference frequently causes off-by-one errors in reporting queries.

NULL behavior in aggregate functions

-- scores: 10, NULL, 30, NULL, 50

SELECT
  COUNT(*)     AS total_rows,       -- 5  (counts all rows)
  COUNT(score) AS non_null_scores,   -- 3  (skips NULLs)
  SUM(score)   AS total,             -- 90 (skips NULLs)
  AVG(score)   AS average            -- 30.00 (90 / 3, not 90 / 5)
FROM quiz_results;

-- Treat NULL as zero in AVG
SELECT AVG(COALESCE(score, 0)) AS average_with_nulls_as_zero  -- 18.00
FROM quiz_results;

Using IS NULL to Find Unmatched Rows in a JOIN

A LEFT JOIN preserves all rows from the left table, filling the right side with NULLs where no match exists. Filtering on IS NULL after a LEFT JOIN is one of the most efficient ways to find records that exist in one table but not another — it avoids a correlated subquery.

NULL Syntax Across SQL Dialects

IS NULL and IS NOT NULL are part of the SQL standard and work identically in every major dialect. The differences appear in convenience functions: SQL Server adds ISNULL, BigQuery and MySQL add IFNULL, and MySQL adds the NULL-safe equality operator <=> for comparing two potentially-NULL values without IS NULL syntax.

NULL Handling Checklist

Run through this list whenever you write or review a query that touches nullable columns:

Replace every = NULL and != NULL with IS NULL and IS NOT NULL.

Check every aggregate (SUM, AVG, MIN, MAX) — decide whether NULLs should be treated as zero or skipped.

Distinguish COUNT(*) from COUNT(column) — they produce different results whenever NULLs are present.

In LEFT JOIN anti-joins, test a non-nullable column (like a primary key) with IS NULL, not a column that might legitimately be NULL.

Wrap denominators in NULLIF(col, 0) to convert zero into NULL and avoid divide-by-zero runtime errors.

Prefer COALESCE over ISNULL or IFNULL when your query needs to run across multiple dialects.

The NULL trap catches everyone eventually. Once you internalize that NULL means unknown — not empty, not zero, not false — the behavior becomes predictable. If you want a formatter and syntax checker to catch issues like these automatically, try sqlfmt — it validates SQL across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery. You might also want to read about common SQL keyword typos, which often appear alongside NULL bugs in the same query.

Anti-join: find customers who have never ordered

-- LEFT JOIN + IS NULL is faster than NOT IN / NOT EXISTS on most engines
SELECT
  c.customer_id,
  c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Always test a non-nullable column (e.g. primary key) in the IS NULL check.
-- If the right-side column can itself be NULL, you may get false positives.

Dialect-specific NULL convenience functions

-- COALESCE: SQL standard — works in all dialects
SELECT COALESCE(phone, 'N/A') FROM contacts;

-- ISNULL: SQL Server / T-SQL only (two-argument shorthand for COALESCE)
SELECT ISNULL(phone, 'N/A') FROM contacts;

-- IFNULL: MySQL and BigQuery
SELECT IFNULL(phone, 'N/A') FROM contacts;

-- <=>: MySQL NULL-safe equality (returns TRUE when both sides are NULL)
SELECT * FROM contacts WHERE phone <=> NULL;  -- equivalent to IS NULL

-- IS NOT DISTINCT FROM: PostgreSQL / SQL standard NULL-safe equality
SELECT * FROM contacts WHERE phone IS NOT DISTINCT FROM NULL;