Real data has gaps. A customer never gave a phone number, a product has not been priced yet, an employee has no manager because she runs the company, a sensor missed a reading. SQL represents every one of those gaps with a single marker, NULL, and gives it rules unlike any other value. Most surprising query results — rows that vanish from a filter, totals that come out empty, a NOT IN that returns nothing — trace back to those rules. This lesson explains what NULL is, how it spreads through expressions, and three-valued logic, the reason conditions involving NULL are neither true nor false.

NULL is not a value

NULL is a marker that says "there is no value here". It is not zero, not the empty string '', not false and not the text 'NULL':

That distinction matters. "Average discount" should count a known zero and ignore an unknown; "customers without a middle name" is a different list from "customers whose middle name we never asked for". A column declared NOT NULL can never hold the marker, which is why schema design (a later module) cares so much about which columns allow it. One dialect note interviewers like: Oracle stores an empty string as NULL, so in Oracle '' is NULL. MySQL, PostgreSQL, SQL Server and SQLite keep the two apart.

NULL spreads through expressions

If a value is unknown, anything computed from it is unknown too:

SELECT 5 + NULL      AS a,   -- NULL
       NULL * 0      AS b,   -- NULL, even times zero
       10 / NULL     AS c,   -- NULL
       CONCAT('Hi ', NULL) AS d;  -- NULL in MySQL

So a line total qty * unit_price is NULL whenever either factor is missing, and a full name built with CONCAT(first_name, ' ', last_name) is NULL for everyone without a last name. (PostgreSQL's || behaves the same; CONCAT in PostgreSQL and SQL Server skips NULLs instead — one of the dialect differences that bites when queries move between databases.) The functions in the next lessons exist largely to stop this spread on purpose.

Comparisons give UNKNOWN

What is NULL = 5? The left side is unknown, so whether it equals 5 is unknown. SQL's answer is neither TRUE nor FALSE but a third truth value, UNKNOWN. MySQL displays UNKNOWN as NULL, because its booleans are numbers (1 true, 0 false) and the unknown one is NULL:

SELECT 5 = 5     AS t,     -- 1
       5 = 6     AS f,     -- 0
       NULL = 5  AS u1,    -- NULL (unknown)
       NULL = NULL AS u2,  -- NULL — not 1!
       NULL <> 5 AS u3;    -- NULL

NULL = NULL being unknown is the rule people find strangest, and it follows directly: two unknown values cannot be known to be equal. Every comparison operator — =, <>, !=, <, >, <=, >=, BETWEEN, LIKE, IN — returns UNKNOWN when a NULL is involved in the comparison it makes.

WHERE keeps only TRUE

A WHERE clause keeps a row when its condition is TRUE. FALSE and UNKNOWN are both thrown away. That one sentence explains most NULL surprises:

SELECT name FROM employees WHERE dept = 'Sales';    -- NULL-dept rows: unknown → dropped
SELECT name FROM employees WHERE dept <> 'Sales';   -- NULL-dept rows: unknown → dropped too!

Run both and add up the rows: employees with no department appear in neither. They are not "Sales" and not "not Sales", as far as SQL can tell. If the business meaning is "everyone except Sales, including people with no department", write the NULL case out:

SELECT name
FROM employees
WHERE dept <> 'Sales' OR dept IS NULL;

IS NULL is the one test that answers a plain yes or no about NULL — it gets its own lesson next. For now remember that dept = NULL is not a test at all: it is UNKNOWN for every row, so WHERE dept = NULL returns nothing, silently.

The three-valued truth tables

When conditions are combined, UNKNOWN combines by a simple principle: if the known parts already decide the answer, the unknown part does not matter; otherwise the answer is unknown.

AND is false as soon as one side is false, and OR is true as soon as one side is true: