Handle NULLs in SQL

NULL is not a value — it is the absence of one

A NULL marks a slot where a value is missing or unknown. It is not zero, not an empty string, and not the boolean false. Two unknown things are not knowably equal, so SQL never reports that one NULL equals another. This single idea — "unknown, not a value" — drives every surprising behaviour on this page.

A column is declared to permit or forbid NULLs at definition time. NOT NULL forbids the gap; the default permits it:

CREATE TABLE students (
    id    INT,
    email VARCHAR(255) NULL,      -- gap allowed
    name  VARCHAR(255) NOT NULL   -- gap forbidden
);

To test the slot itself you must use the dedicated predicates IS NULL / IS NOT NULL — never = NULL, which can only ever be unknown and so matches nothing:

SELECT * FROM students WHERE email IS NULL;

Three-valued logic: TRUE, FALSE, and UNKNOWN

Because NULL means "unknown", any comparison that touches a NULL yields a third truth value, UNKNOWN, rather than TRUE or FALSE. SQL's WHERE, ON, and HAVING clauses are three-valued: a row or group is kept only when its predicate evaluates to TRUE. Both FALSE and UNKNOWN cause it to be dropped.

The truth tables for the connectives follow Kleene's strong three-valued logic. Note the two cases where a NULL operand does not poison the result: TRUE OR UNKNOWN is TRUE (one true disjunct is enough), and FALSE AND UNKNOWN is FALSE (one false conjunct is enough).

NULLs in aggregates, GROUP BY, and ORDER BY

Aggregate functions skip NULLs — with one deliberate exception. COUNT(*) counts rows regardless of content, but COUNT(col), SUM, AVG, MIN, and MAX all ignore NULL inputs. This is why AVG(salary) divides by the number of non-NULL salaries, not by the row count — a frequent source of "wrong average" bugs.

-- 5 rows, 2 of them NULL bonus
SELECT COUNT(*)      AS rows,      -- 5
       COUNT(bonus)  AS with_bonus,-- 3
       SUM(bonus)    AS total,     -- sum of the 3 non-NULLs
       AVG(bonus)    AS mean       -- total / 3, NOT total / 5
FROM employees;

For grouping and sorting, SQL treats all NULLs as a single "unknown" bucket even though they are not equal under comparison: GROUP BY collapses every NULL into one group, and DISTINCT keeps just one NULL. Ordering is engine-specific — PostgreSQL and Oracle sort NULLs last by default, MySQL and SQL Server sort them first — so spell it out with ORDER BY col NULLS LAST (or NULLS FIRST) where the dialect supports it.

Replacing NULLs: COALESCE, NULLIF, and friends

The portable tool is COALESCE(a, b, c, …): it returns the first non-NULL argument, left to right. Use it to supply a default at read time:

SELECT name, COALESCE(phone, mobile, 'no contact') AS reach
FROM customers;

• COALESCE — ANSI standard, variadic, works everywhere. Prefer it.
• NULLIF(a, b) — returns NULL when a = b, else a. The classic use is guarding division: x / NULLIF(y, 0) yields NULL instead of a divide-by-zero error.
• IFNULL(a, b) (MySQL) and ISNULL(a, b) (SQL Server) — two-argument, dialect-specific shortcuts for COALESCE. Oracle's historical equivalent is NVL(a, b).

Going the other way, an arithmetic expression with a NULL operand is itself NULL: 10 + NULL is NULL, and salary * NULL is NULL. Substitute before computing — COALESCE(salary, 0) * 1.1 — when a missing value should behave like zero.

Common pitfalls

• NOT IN with a NULL in the list returns no rows. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL; the last conjunct is UNKNOWN, so the whole predicate can never be TRUE. Prefer NOT EXISTS or filter the NULLs out of the subquery.
• String concatenation can blank the whole string — but the behaviour is dialect-specific. In PostgreSQL, 'Hi ' || lastname yields NULL when lastname is NULL, and in SQL Server, 'Hi ' + lastname does the same when CONCAT_NULL_YIELDS_NULL is ON (the default). Oracle is the textbook exception: its || operator treats a NULL operand as an empty string, so 'Hi ' || NULL returns 'Hi ', not NULL — Oracle famously equates the empty string with NULL. The portable fix is the CONCAT(...) function, which treats NULL arguments as empty strings on every engine.
• Equality never matches NULL. WHERE col = NULL and WHERE col <> NULL both evaluate to UNKNOWN and return nothing. Use IS NULL / IS NOT NULL, or the null-safe equality <=> in MySQL and IS NOT DISTINCT FROM in PostgreSQL.
• A CHECK constraint does not reject NULLs. Because CHECK is satisfied on TRUE or UNKNOWN, a row with a NULL in the checked column passes. If the column must also be present, add NOT NULL alongside the CHECK.

Source

Adapted and expanded from the "Handle NULLs in SQL" lesson in the SQL Fundamentals track, cross-checked against the ISO/IEC 9075 SQL standard's three-valued logic and the official documentation for PostgreSQL, MySQL, Oracle Database, and SQL Server (T-SQL) for the dialect-specific concatenation, ordering, and null-handling behaviours.