# SQL IS NULL and IS NOT NULL: Find Missing Values

- URL: https://betteranalyst.com/sql/is-null

SQL guide · Filtering

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

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL NOT](https://betteranalyst.com/sql/not)
- [SQL LEFT JOIN](https://betteranalyst.com/sql/left-join)
- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL CASE](https://betteranalyst.com/sql/case)
- [SQL EXISTS](https://betteranalyst.com/sql/exists)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)