Working with NULL

NULL is not zero. It is not an empty string. It is not false. It means unknown — the database has no value here — and treating it as anything else is the origin of more quietly wrong SQL than any other single cause.

Three-valued logic

Most languages have true and false. SQL has true, false, and unknown.

SQL▶ Run
SELECT NULL = NULL      AS equals,
       NULL <> NULL     AS not_equals,
       NULL IS NULL     AS is_null;

The first two come back NULL — not true, not false, unknown. Only the third is 1. This is not a quirk, it is the definition: if two salaries are both unknown, is one equal to the other? The honest answer is that nobody knows.

The consequence is a rule with no exceptions: WHERE keeps rows where the condition is true. Unknown is not true, so the row is dropped — and no error is raised anywhere along the way.

Truth tables

AND — false wins, because one false makes the whole thing false regardless:

OR — true wins, for the mirror reason:

NOT unknown is unknown. You cannot negate your way out of it.

Testing for NULL

IS NULL and IS NOT NULL are the only tests that work:

SQL▶ Run
SELECT first_name, salary
FROM employees
WHERE salary IS NULL;

WHERE salary = NULL returns nothing — not an error, just an empty result, which looks exactly like "nobody matched".

NULL propagates through arithmetic

SQL▶ Run
SELECT 1 + NULL AS arithmetic, 'a' || NULL AS concatenation;

Both NULL. Any expression with an unknown input has an unknown result. So a bonus calculation of salary * 1.1 is NULL for the employee with no salary, and a report built from first_name || ' — ' || email silently loses the row with no email address. (SQL Server's + and MySQL's CONCAT behave the same way; MySQL's CONCAT_WS is the exception and skips NULLs.)

Aggregates skip NULLs — except COUNT(*)

This is where NULL does real financial damage.

SQL▶ Run
SELECT COUNT(*)       AS employees,
       COUNT(salary)  AS with_salary,
       SUM(salary)    AS total,
       AVG(salary)    AS average
FROM employees;

Ten employees, nine salaries. SUM and AVG ignore the tenth row entirely, so the average is the total divided by nine, not ten. Whether that is right depends on what the NULL means: an employee whose salary is not recorded should probably be excluded, but an unpaid intern whose salary is genuinely zero should be counted — and stored as 0, not NULL.

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. The gap between those two numbers is the fastest data-quality check there is.

COUNT(DISTINCT dept_id) returns 3, not 4 — DISTINCT inside an aggregate discards NULL as well.

Replacing NULL: COALESCE

COALESCE returns the first argument that is not NULL:

SQL▶ Run
SELECT first_name,
       COALESCE(email, 'no email on file')  AS contact,
       COALESCE(salary, 0)                  AS salary_for_report
FROM employees
ORDER BY emp_id;

COALESCE is standard and takes any number of arguments, so it works everywhere. The per-database two-argument shorthands — IFNULL (MySQL, SQLite), ISNULL (SQL Server), NVL (Oracle) — all do the same job less portably.

Be careful what you substitute: COALESCE(salary, 0) inside an AVG changes the answer, because the row is no longer skipped. Substituting zero is a display decision, not a maths one.

The inverse, NULLIF(a, b), returns NULL when the two are equal — most often used as NULLIF(divisor, 0) to turn a division-by-zero error into a NULL result.

Sorting NULLs

ORDER BY has to put unknowns somewhere, and databases disagree about where: PostgreSQL and Oracle sort them last ascending, MySQL and SQL Server sort them first. NULLS FIRST / NULLS LAST makes it explicit on PostgreSQL and Oracle; elsewhere, sort by CASE WHEN col IS NULL THEN 1 ELSE 0 END first.

Where NULLs come from

• A column that was never filled in.
• A LEFT JOIN with no match on the right — every column from the right side is NULL.
• An aggregate over zero rows: SUM of nothing is NULL, though COUNT of nothing is 0.
• An outer-joined or filtered-away subquery result.

The second one is why the joins module comes back to this lesson.

Common mistakes

• = NULL instead of IS NULL — matches nothing, warns nobody.
• Expecting <> or NOT IN to include NULLs — they never do.
• Reading AVG as "total ÷ row count" — it is total ÷ non-NULL count.
• COALESCE-ing before aggregating without meaning to — it changes the answer.
• Storing '' or 0 or -1 for "unknown" — now every query needs to know your private convention, and the database's own NULL handling cannot help you.

Interview question

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL. A strong answer continues: the difference tells you how many values are missing, and SUM/AVG skip those rows too.

Check yourself

1. Why does WHERE salary <> 90000 omit employees with no salary?
2. What is NULL OR true? What is NULL AND false?
3. When would COALESCE(salary, 0) give a misleading average?

Module complete

You can now filter precisely: comparisons, compound conditions with the right brackets, ranges, patterns, and unknowns. Next: grouping rows and computing aggregates over them — where the NULL rules you just learned start affecting totals people rely on.