SQL NULL Handling Best Practices

NULL is one of the most misunderstood parts of SQL. It is not zero, not an empty string, and not a value at all — it represents the absence of a known value. That single fact causes more bugs than almost any other SQL concept, from queries that silently return zero rows to reports that quietly drop entire groups of data. This guide explains how NULL actually behaves, how to test for it correctly, and how to handle it safely in comparisons, aggregates, and joins.

Three-Valued Logic: TRUE, FALSE, and UNKNOWN

Most programming languages use two-valued (boolean) logic — an expression is either TRUE or FALSE. SQL uses three-valued logic: an expression can be TRUE, FALSE, or UNKNOWN. Any comparison where one side is NULL evaluates to UNKNOWN, because SQL cannot know whether an unknown value is equal to, greater than, or less than anything else.

NULL = NULL        -- UNKNOWN (not TRUE!)
NULL = 5            -- UNKNOWN
NULL <> 5            -- UNKNOWN
5 > NULL             -- UNKNOWN
NULL AND TRUE         -- NULL (UNKNOWN)
NULL OR TRUE           -- TRUE
NULL OR FALSE           -- NULL (UNKNOWN)

A WHERE clause only keeps rows where the condition evaluates to TRUE. Rows that evaluate to FALSE or UNKNOWN are both excluded — which is exactly why comparisons against NULL quietly filter out rows instead of raising an error.

The Classic Pitfall: WHERE column = NULL

This is the single most common NULL-related bug in SQL. Developers new to SQL often write WHERE discount = NULL expecting it to match rows where discount has no value. It never does — because discount = NULL evaluates to UNKNOWN for every row, even rows where discount genuinely is NULL.

-- WRONG: returns zero rows, always
SELECT order_id, discount
FROM orders
WHERE discount = NULL;

-- CORRECT: use IS NULL to test for missing values
SELECT order_id, discount
FROM orders
WHERE discount IS NULL;

-- CORRECT: use IS NOT NULL for the opposite check
SELECT order_id, discount
FROM orders
WHERE discount IS NOT NULL;

The same rule applies to != and <>. A query like WHERE status <> NULL will never match anything, and worse, it can silently exclude legitimate rows if you meant to check WHERE status IS DISTINCT FROM 'cancelled' (PostgreSQL) or an equivalent NULL-safe comparison instead.

COALESCE, IFNULL, and ISNULL — Substituting Default Values

To replace a NULL with a fallback value, use COALESCE() — the ANSI SQL standard function supported by MySQL, PostgreSQL, and SQL Server. It accepts two or more arguments and returns the first one that is not NULL.

-- Works in MySQL, PostgreSQL, SQL Server, SQLite
SELECT
  customer_name,
  COALESCE(phone, mobile, 'No contact on file') AS contact_number
FROM customers;

-- COALESCE tries each argument left to right
-- and returns the first non-NULL value it finds

Some databases also offer a two-argument shorthand:

• MySQL — IFNULL(expr, replacement), e.g. IFNULL(discount, 0)
• SQL Server — ISNULL(expr, replacement), e.g. ISNULL(discount, 0)
• PostgreSQL — no IFNULL/ISNULL; use COALESCE(discount, 0) instead
• Oracle — NVL(expr, replacement)

-- MySQL
SELECT product_name, IFNULL(discount, 0) AS discount FROM products;

-- PostgreSQL (portable, ANSI standard)
SELECT product_name, COALESCE(discount, 0) AS discount FROM products;

Since COALESCE works identically across every major database, it is the safer default choice when you want your SQL to be portable.

NULL in Aggregates, JOINs, and Sorting

NULL behaves differently depending on where it appears. These are the behaviors that catch developers off guard most often:

• COUNT(*) counts all rows including NULLs, but COUNT(column) skips rows where that column is NULL
• SUM, AVG, MAX, MIN all silently ignore NULL values rather than treating them as zero
• INNER JOIN drops rows where the join column is NULL on either side, since NULL never equals NULL
• ORDER BY places NULLs first in MySQL and PostgreSQL by default (ascending order); use NULLS LAST in PostgreSQL to control this explicitly
• UNIQUE constraints in most databases allow multiple NULLs, because NULL is never considered equal to another NULL

-- COUNT(*) vs COUNT(column)
SELECT
  COUNT(*) AS total_rows,
  COUNT(discount) AS rows_with_discount
FROM orders;

-- PostgreSQL: force NULLs to sort last
SELECT customer_name, last_login
FROM customers
ORDER BY last_login DESC NULLS LAST;

NULL-Safe Equality Checks

Sometimes you genuinely need to compare two columns and treat NULL as equal to NULL — for example, matching rows across two tables where a missing value in both places should count as a match. Standard = cannot do this, so each database provides a NULL-safe operator:

-- MySQL: NULL-safe equality operator
SELECT * FROM employees a
JOIN employees b ON a.manager_id <=> b.manager_id
WHERE a.id <> b.id;

-- PostgreSQL and SQL Server standard: IS NOT DISTINCT FROM
SELECT * FROM employees a
JOIN employees b ON a.manager_id IS NOT DISTINCT FROM b.manager_id
WHERE a.id <> b.id;

These operators return TRUE when both sides are NULL, unlike the regular = operator which always returns UNKNOWN in that case.

Best Practices Checklist

1. Always use IS NULL / IS NOT NULL — never = NULL or <> NULL
2. Use COALESCE() for portable default-value substitution across databases
3. Remember that COUNT(column) and COUNT(*) behave differently when NULLs are present
4. Check whether your JOIN conditions could unintentionally drop rows because of NULL join keys
5. Decide explicitly whether NULLs should sort first or last, rather than relying on the database default
6. Prefer NOT NULL constraints with sensible defaults at the schema level when a column should never be genuinely unknown

Frequently Asked Questions