Filtering Updated November 2026

IS NULL

NULL is the absence of a value and no comparison with = or <> is ever true for it. To find rows with a missing value write WHERE column IS NULL, and to exclude them WHERE column IS NOT NULL. COUNT(column) skips NULLs while COUNT(*) counts every row.

Syntax

WHERE column IS NULL | WHERE column IS NOT NULL

Examples

SELECT name FROM employees WHERE manager_id IS NULL;

Result: Employees with no manager (typically the CEO)

SELECT id FROM customers WHERE email IS NOT NULL AND email <> '';

Result: Customers with a real email address

Empty string and NULL are different; check both when data is messy.

SELECT COUNT(*) AS all_rows, COUNT(phone) AS with_phone FROM customers;

Result: all_rows | with_phone 120 | 95

25 customers have a NULL phone.

Common mistakes

WHERE x = NULL or WHERE x != NULL returns zero rows every time; use IS NULL / IS NOT NULL.
WHERE status <> 'paid' drops rows where status is NULL, because NULL <> 'paid' is unknown; add OR status IS NULL if those rows belong in the result.
Arithmetic with NULL gives NULL (price + NULL is NULL), so totals go missing; wrap with COALESCE(bonus, 0).

Practice IS NULL

These drills check your answer on live data, free, no account needed.

No managerReturn the names of employees who have no manager.
Customers who never orderedReturn the names of customers who have no orders at all.

Questions

Why does NULL = NULL return false?

It returns NULL (unknown), not false. Two unknowns are not known to be equal. PostgreSQL offers IS NOT DISTINCT FROM and SQLite offers IS to compare values treating NULLs as equal.

How does ORDER BY treat NULL?

SQLite sorts NULLs first in ascending order; PostgreSQL sorts them last. Both accept NULLS FIRST or NULLS LAST to control it.

COALESCECOALESCE returns the first non-NULL argument. Show a default instead of NULL, treat missing numbers as zero in sums, fall back across columns, IFNULL, NULLIF.
WHEREUse WHERE to keep only the rows that match a condition: equals, greater than, text matches, NULL checks, and combinations with AND, OR and NOT.
COUNTCOUNT(*) counts rows, COUNT(column) counts non-NULL values, COUNT(DISTINCT column) counts unique values. Examples with WHERE, GROUP BY and conditional counts.
LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.