NULL in SQL Does Not Mean What You Think It Means

The filter that silently excludes rows

Your query is supposed to return all orders that are not cancelled. You write WHERE cancelled = false. In production, orders where cancelled is NULL — representing orders created before the cancelled column was added — are silently excluded. You've lost records from your report without any error or warning.

NULL in SQL is not a value. It is the absence of a value — the database's way of representing "unknown" or "not applicable." This distinction has concrete consequences: NULL behaves differently in comparisons, in aggregations, in joins, and in boolean logic. Getting it wrong produces queries that return wrong results silently.

NULL is not false, and NULL is not zero

The most common NULL misconception: treating it as a falsy value in filter conditions.

-- A column where some rows have NULL
-- (e.g., discount_pct is NULL when no discount applies)

-- Intended: "orders with no discount"
WHERE discount_pct = 0     -- Returns rows where discount_pct is exactly 0
                           -- Does NOT return rows where discount_pct is NULL

WHERE discount_pct = NULL  -- Returns NOTHING. Always. This is always false.
                           -- You cannot compare with = NULL

-- Correct: check for NULL explicitly
WHERE discount_pct IS NULL
WHERE discount_pct IS NOT NULL

-- Correct: "orders with no discount OR discount of 0"
WHERE discount_pct IS NULL OR discount_pct = 0

-- Using COALESCE to normalize NULL to a default value
WHERE COALESCE(discount_pct, 0) = 0

= NULL is never true because NULL means "unknown." An unknown value compared to anything else is unknown, not true or false. This is three-valued logic: true, false, and unknown.

Three-valued logic and WHERE clauses

SQL WHERE clauses return rows only when the condition evaluates to TRUE. Conditions that evaluate to FALSE or UNKNOWN (NULL) are excluded. This is why WHERE column = NULL returns nothing — the comparison evaluates to UNKNOWN, not TRUE.

-- Suppose: column = NULL for some rows, column = 5 for others

WHERE column = 5      -- TRUE for rows where column = 5, UNKNOWN for NULL rows
WHERE column != 5     -- TRUE for rows where column != 5, UNKNOWN for NULL rows
WHERE column IS NULL  -- TRUE for NULL rows, FALSE for others
WHERE column IS NOT NULL -- FALSE for NULL rows, TRUE for others

The implication: WHERE column != 5 does NOT return NULL rows. Both = 5 and != 5 exclude NULLs. If you want "rows where column is not 5, including nulls":

WHERE column != 5 OR column IS NULL
-- Or equivalently:
WHERE NOT (column = 5)  -- Still excludes NULLs
-- This is NOT the same as: WHERE column != 5 OR column IS NULL

The NOT operator also propagates NULL: NOT NULL evaluates to NULL (unknown), not TRUE.

NULL propagation in expressions

Any arithmetic or string operation involving NULL produces NULL:

SELECT
  5 + NULL,          -- NULL
  'hello' || NULL,   -- NULL (string concatenation)
  NULL * 100,        -- NULL
  CASE WHEN NULL THEN 'yes' ELSE 'no' END  -- 'no' (WHEN NULL is UNKNOWN = false)

This means that if any input to a calculation is NULL, the entire expression becomes NULL. In revenue calculations, this is dangerous:

-- If unit_price or quantity is NULL, the total is NULL (not zero)
SELECT unit_price * quantity AS line_total FROM order_items;

-- Safe version: use COALESCE to provide defaults
SELECT COALESCE(unit_price, 0) * COALESCE(quantity, 0) AS line_total FROM order_items;

NULL in aggregations

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) ignore NULLs, except COUNT(*) which counts all rows:

-- A column with values: 10, 20, NULL, 30, NULL
SELECT
  COUNT(*),           -- 5 (counts all rows including NULLs)
  COUNT(amount),      -- 3 (counts only non-NULL rows)
  SUM(amount),        -- 60 (ignores NULLs — equivalent to 10+20+30)
  AVG(amount),        -- 20 (60 / 3, not 60 / 5 — NULLs excluded from denominator)
  MIN(amount),        -- 10
  MAX(amount)         -- 30
FROM payments;

The AVG behavior is worth highlighting: it divides by the count of non-NULL values, not the total row count. If 40% of rows are NULL, the average reflects only the non-NULL population. Whether this is correct depends on your business question — sometimes you want SUM(amount) / COUNT(*) (treating NULL as zero for the average).

NULL in JOINs

NULL values in join columns do not match each other:

-- If user_id is NULL in some orders
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;
-- Orders with NULL user_id are excluded — NULL != NULL

This is correct behavior for FK columns (NULL user_id means "no user"), but it can surprise developers who expect NULL to match NULL in some other context.

NULL ordering

In ORDER BY, NULL sorts as larger than any other value in PostgreSQL (NULLs sort last in ASC, first in DESC). SQL Server and MySQL sort NULLs first in ASC. This is database-specific and matters for reports that depend on sort order:

-- PostgreSQL: explicit NULL placement
ORDER BY amount DESC NULLS LAST   -- Put NULLs at the end regardless of sort direction
ORDER BY amount ASC NULLS FIRST

NULL in UNIQUE constraints

Most databases allow multiple NULLs in a UNIQUE-constrained column, because NULL != NULL — each NULL is "unknown" and therefore not the same as any other NULL. PostgreSQL, MySQL, and SQL Server all allow this. If you need at-most-one-NULL semantics, use a partial unique index:

-- PostgreSQL: unique where not null
CREATE UNIQUE INDEX idx_users_referral_code
ON users (referral_code)
WHERE referral_code IS NOT NULL;

The practical rule

Treat NULL as a special case to handle explicitly. Use IS NULL and IS NOT NULL for filtering. Use COALESCE(value, default) to provide defaults in calculations. Use NULLIF(value, other) to convert a specific value to NULL (the inverse of COALESCE). Never compare to NULL with = or !=. When designing schemas, be deliberate about which columns allow NULL and document what NULL means for each one — "no value set," "not applicable," or "unknown" each have different query implications.