NULL Values

NULL represents a missing or unknown value: a customer who didn't give a city, a product with no discount yet. It isn't zero and it isn't an empty string — and it follows special rules that cause more SQL bugs than anything else. This lesson makes those rules clear.

NULL is not a value you can compare

SELECT name FROM customers WHERE city = NULL;

No rows, even though Farah's city is NULL. Comparing anything with NULL — even NULL = NULL — gives unknown, and WHERE only keeps rows where the condition is true.

SELECT NULL = NULL AS equal, NULL <> 1 AS not_equal, 1 + NULL AS sum;

IS NULL and IS NOT NULL

SELECT name, city FROM customers WHERE city IS NULL;

SELECT name, email FROM customers WHERE email IS NOT NULL AND country = 'Portugal';

These special operators are the correct way to test for missing values.

NULL spreads through expressions

SELECT name, city, 'Lives in ' || city AS description
FROM customers
WHERE country IN ('France', 'Spain');

Any arithmetic or concatenation involving NULL produces NULL. (Oracle is an exception for strings: it treats an empty string as NULL and || ignores it.)

COALESCE: supplying a default

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers
WHERE country = 'France';

COALESCE returns its first argument that isn't NULL. It accepts any number of arguments: COALESCE(mobile, landline, email, 'no contact'). Database-specific equivalents include IFNULL (MySQL, SQLite), ISNULL (SQL Server) and NVL (Oracle).

NULLIF: turning a value into NULL

-- 6 units of each product are kept for display.
-- What percentage of the current stock is that?
SELECT name, stock, ROUND(100.0 * 6 / NULLIF(stock, 0), 1) AS pct_reserved
FROM products
WHERE category = 'Electronics';

NULLIF(a, b) returns NULL if a equals b, otherwise a. Its classic use is avoiding division by zero. (SQLite returns NULL for division by zero anyway; most other databases raise an error.)

NULL in aggregates

SELECT COUNT(*) AS customers,
       COUNT(city) AS with_city,
       COUNT(email) AS with_email
FROM customers;

COUNT(*) counts rows; COUNT(column) counts non-NULL values. SUM, AVG, MIN and MAX also skip NULLs (lesson 18).

NOT and NULL

SELECT name, city FROM customers WHERE city <> 'Lisbon';

Farah isn't in the result: her city isn't Lisbon, but NULL <> 'Lisbon' is unknown. If you want rows with no city too, say so: WHERE city <> 'Lisbon' OR city IS NULL.

IS DISTINCT FROM: NULL-safe comparison

SELECT name, city
FROM customers
WHERE city IS DISTINCT FROM 'Lisbon';

The standard IS [NOT] DISTINCT FROM treats NULL as an ordinary comparable value. It's supported by PostgreSQL, SQLite (3.39+) and SQL Server 2022; MySQL uses <=> for "null-safe equals". SQLite also allows IS and IS NOT with any value.

Should a column allow NULL?

When designing tables, mark columns NOT NULL unless a missing value is genuinely meaningful (lesson 42). Fewer NULLs mean fewer surprises in every query that touches the column.

Try it yourself

List all customers with their email, showing "(none)" when it's missing.