---
title: "NULL"
description: "NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL."
canonical: "https://motherduck.com/glossary/null-value/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Big Data is Dead"
    url: "https://motherduck.com/blog/big-data-is-dead/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# NULL

> NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.

## Overview
`NULL` is SQL's marker for the absence of a value. It doesn't mean zero, an empty string, or false -- it means "unknown" or "not applicable." This distinction drives a lot of behavior that's surprising to newcomers: arithmetic, comparisons, and even equality checks behave differently once `NULL` is involved.

## Three-valued logic
SQL uses three-valued logic: any expression can be `TRUE`, `FALSE`, or `UNKNOWN` (`NULL`). Comparing anything to `NULL` with `=` or `<>` produces `NULL`, not `TRUE` or `FALSE`:

```sql
SELECT NULL = NULL;      -- NULL (unknown), not TRUE
SELECT NULL <> 5;        -- NULL
SELECT 5 = NULL;         -- NULL
```
A `WHERE` clause only keeps rows where the condition is `TRUE`, so `WHERE column = NULL` never matches anything -- even rows where `column` is `NULL`. Use `IS NULL` / `IS NOT NULL` instead:

```sql
SELECT * FROM orders WHERE shipped_date IS NULL;       -- correct
SELECT * FROM orders WHERE shipped_date = NULL;         -- always returns zero rows
```

## NULL in arithmetic and aggregation
Any arithmetic operation involving `NULL` produces `NULL` (`5 + NULL` is `NULL`). Most aggregate functions (`SUM`, `AVG`, `COUNT(column)`, `MAX`, `MIN`) ignore `NULL` values rather than treating them as zero -- `COUNT(*)` is the exception, since it counts rows regardless of `NULL`s.

## Handling NULL
`COALESCE(expr, default)` substitutes a value when an expression is `NULL`. `NULLIF(a, b)` does the reverse, turning a specific value into `NULL`. `IS [NOT] DISTINCT FROM` provides a null-safe equality comparison that treats two `NULL`s as equal, unlike `=`.

## DuckDB notes
DuckDB follows standard SQL `NULL` semantics throughout, including in joins (a `NULL` join key never matches another `NULL` join key with `=`) and sorting, where DuckDB places `NULL`s last by default in ascending `ORDER BY` (configurable with `NULLS FIRST` / `NULLS LAST`).