SQL guide · Filtering

SQL IS NULL and IS NOT NULL: Find Missing Values

NULL in SQL means a missing or unknown value. It is not zero and not an empty string. Because an unknown value is not equal to anything, not even another NULL, ordinary comparison operators cannot detect it. IS NULL and IS NOT NULL exist for exactly this purpose.

column IS NULL is true when the value is missing, and column IS NOT NULL is true when a value is present. Use them in WHERE to find incomplete records, in JOIN results to spot unmatched rows from an outer join, and in CASE expressions to handle gaps.

The deeper rule is three-valued logic. A comparison with NULL returns unknown, and WHERE keeps only rows that are true. This is why col = NULL returns nothing, why col <> 'x' drops rows where col is NULL, and why aggregate functions like COUNT(col) skip NULLs while COUNT(*) does not.

Basic syntax

SELECT column1
FROM table_name
WHERE column IS NULL;

SELECT column1
FROM table_name
WHERE column IS NOT NULL;

IS NULL and IS NOT NULL are the only reliable predicates for testing missingness. To substitute a default, use COALESCE(column, default), which returns the first non-NULL argument. Never use = NULL or <> NULL.

SQL IS NULL examples

Find rows with a missing value

This lists customers who have no phone number on file. An empty string would not be matched, so check how your data represents blanks.

SELECT customer_id, name
FROM customers
WHERE phone IS NULL;

Keep only rows with a value

IS NOT NULL is the reverse. This returns orders that have already shipped, meaning the shipped date was recorded.

SELECT order_id, shipped_date
FROM orders
WHERE shipped_date IS NOT NULL;

Anti-join with IS NULL

After a LEFT JOIN, unmatched rows carry NULL in the right-hand columns. Filtering on that finds customers who have no orders at all.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Replace NULL with a default

COALESCE returns the first non-NULL value, which keeps reports from showing blanks and prevents NULL arithmetic from wiping out a total.

SELECT employee_id,
       salary + COALESCE(bonus, 0) AS total_pay
FROM employees;

Where SQL dialects differ

PostgreSQL: Supports IS DISTINCT FROM and IS NOT DISTINCT FROM for NULL-safe equality. Empty strings and NULL are different values.

MySQL: Provides the NULL-safe equality operator <=>. For DATE columns with zero dates, IS NULL can behave unexpectedly under some modes. Use IFNULL() or COALESCE() for defaults.

SQL Server: Uses ISNULL(a, b) as a two-argument alternative to COALESCE. With ANSI_NULLS OFF (deprecated) = NULL behaves like IS NULL, so avoid relying on it.

SQLite: Supports IS and IS NOT with any operands as NULL-safe comparison, for example a IS b. IFNULL() and COALESCE() both work.

Oracle: An empty string is stored as NULL, so col = '' never matches and col IS NULL catches both. NVL(a, b) is the common two-argument replacement function.

Common mistakes

  • Writing WHERE col = NULL or col <> NULL. Both are unknown for every row, so nothing is returned.
  • Forgetting that col <> 'x' excludes NULL rows. Add OR col IS NULL if you want them.
  • Treating NULL as zero or empty. NULL + 5 is NULL, and a blank string is a real value.
  • Using NOT IN against a column that contains NULL, which returns no rows at all.
  • Counting with COUNT(col) and expecting all rows. It skips NULLs; use COUNT(*) for the row total.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator