How Does NULL Behave in SQL?

NULL is the marker for "no value here" — the absence of a value, not a value in itself. That distinction drives everything surprising about it. Because NULL means unknown, no comparison against it can ever be definitively true, so every comparison involving NULL evaluates to UNKNOWN rather than TRUE or FALSE. Even NULL compared with NULL is UNKNOWN, because the engine cannot know that two unknowns are equal.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  manager_id INTEGER
);

INSERT INTO staff (id, name, manager_id) VALUES
  (1, 'Ada Lovelace', NULL),
  (2, 'Grace Hopper', 1),
  (3, 'Alan Turing', NULL);

-- NULL = NULL is UNKNOWN, so this returns 0, not 2.
SELECT COUNT(*) AS null_equals_null FROM staff WHERE manager_id = manager_id;

-- IS NULL is the only correct way to test for a missing value.
SELECT COUNT(*) AS top_level FROM staff WHERE manager_id IS NULL;
SELECT COUNT(*) AS has_manager FROM staff WHERE manager_id IS NOT NULL;

The three counts come back 0, 2 and 1. The first is the headline: a query asking "which rows have manager_id equal to their own manager_id" returns nothing at all, because every comparison is UNKNOWN and WHERE keeps only rows that are definitively TRUE.

Three-valued logic also decides what happens in aggregates and in boolean expressions. SUM, AVG and COUNT ignore NULLs, so AVG over a column with two missing values averages the values that are present, not zero for the missing ones. A WHERE clause discards UNKNOWN rows entirely, but a HAVING or a CHECK constraint treats UNKNOWN as satisfied — a CHECK constraint only rejects a row when the condition is definitely FALSE. That is why CHECK (discount > 0) lets a NULL discount through: the engine does not know that it is wrong.

Finally, NULL propagates. Any arithmetic with a NULL operand yields NULL, and most functions do too, with a handful of exceptions such as COALESCE and NULLIF that exist precisely to handle it deliberately.

An empty string and a NULL are different things: the empty string is a real, known, zero-length value, and NULL is the absence of a value. Most application bugs here come from writing = '' when the intent was "this field was never filled in", or from writing IS NULL when the intent was "this field is blank".

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200)
);

INSERT INTO customers (id, email) VALUES
  (1, '[email protected]'),
  (2, ''),
  (3, NULL);

-- The value is missing entirely: one row, id 3.
SELECT id, email FROM customers WHERE email IS NULL;

-- The value is present but empty: one row, id 2.
SELECT id, email FROM customers WHERE email = '';

-- Rows that carry any value at all: two rows, ids 1 and 2.
SELECT COUNT(*) AS populated_rows FROM customers WHERE email IS NOT NULL;

The first SELECT returns id 3, the second returns id 2, and the count is 2. Notice what the empty string is not: it is not NULL, so it survives an IS NOT NULL test, and it is not equal to NULL, so it never matches IS NULL.

Comparing against NULL with an equality operator is not just a syntax mistake, it is a semantic one. email = NULL is UNKNOWN for every row, so the predicate matches nothing at all. The only two correct forms are IS NULL and IS NOT NULL; the IS operator exists precisely because equality cannot express the question.

When a report needs to treat "blank for reporting purposes" as one bucket, combine them explicitly rather than assuming the column is uniform: WHERE email IS NULL OR email = '', or WHERE COALESCE(email, '') = ''. Choosing between NULL and the empty string in the schema is a real design decision — NOT NULL plus the empty string is simpler to work with, and reserving NULL for "not applicable" keeps the two meanings distinct.