What NULL Really Means

Concept

NULL is not a value. It is a marker meaning "no value here" - unknown, missing or not applicable. That single distinction explains every strange behaviour that follows.

Syntax

-- The only two tests that work
WHERE column_name IS NULL
WHERE column_name IS NOT NULL

-- These never match anything
WHERE column_name =  NULL
WHERE column_name <> NULL

Example

-- In the sample data, Nikhil has no dept_id and no email
SELECT first_name, dept_id, email FROM employees WHERE dept_id IS NULL;

-- NULL is contagious in expressions
SELECT 10 + NULL          AS arithmetic,   -- NULL
       CONCAT('a', NULL)  AS concatenated, -- NULL in MySQL
       NULL = NULL        AS is_equal;     -- NULL, not 1

-- Empty string and NULL are different things
SELECT COUNT(*)                                   AS total,
       COUNT(email)                               AS emails_present,
       SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS emails_missing
FROM   employees;

Explanation

NULL = NULL returning NULL rather than true is the rule everything else follows from. If two people's phone numbers are both unknown, SQL cannot honestly claim they are the same number - so the answer is "unknown", not "yes".

Notice COUNT(*) versus COUNT(email) in the last query: COUNT(*) counts rows, COUNT(column) counts non NULL values in that column. The difference between them is exactly the number of missing emails.

Where NULL behaves inconsistently

Read that table twice. GROUP BY and DISTINCT treat NULLs as equal, while = treats them as not comparable. Both behaviours are in the standard, and both are intentional.

Important rules

• Any arithmetic or string operation involving NULL returns NULL.
• Any comparison with NULL returns UNKNOWN, so the row fails WHERE.
• Aggregates skip NULLs, which makes AVG the average of the present values, not of all rows.
• COUNT(*) counts rows; COUNT(expr) counts non NULL results.
• MySQL offers <=>, a NULL safe equality where NULL <=> NULL is true. PostgreSQL and standard SQL spell it IS NOT DISTINCT FROM.

Common mistakes

• Writing = NULL and concluding there are no missing values.
• Expecting WHERE status <> 'active' to include rows where status is NULL.
• Using NOT IN against a subquery whose column contains a NULL, and getting an empty result.
• Treating NULL and '' as interchangeable - and then having two kinds of "empty" in one column forever.
• Reporting AVG(bonus) as "the average bonus per employee" when half the employees have no bonus row at all.

Best practices

• Declare columns NOT NULL unless unknown is a genuine, meaningful state for that column.
• Pick one representation for "nothing" per column - NULL or '', never both - and enforce it with a constraint.
• Wrap nullable columns in COALESCE whenever they feed arithmetic or concatenation.
• Say what you mean about NULL in reports: AVG(COALESCE(bonus, 0)) and AVG(bonus) answer different questions.

Practice

1. Count how many employees have no department, using two different queries.
2. Explain why SELECT COUNT(email) FROM employees and SELECT COUNT(*) FROM employees differ.
3. Write a query returning every employee's email or the text 'not provided'.