SQL NULL Values

In SQL, NULL represents a missing, unknown, or inapplicable value — it is not the same as zero, an empty string, or false. A column holding NULL simply means "we don’t know" or "there is nothing here." Because NULL behaves differently from every other value in comparisons, arithmetic, and aggregation, it trips up beginners constantly. This lesson explains exactly how NULL works under the hood and how to handle it correctly in real queries.

Overview: How NULL Works

NULL is a special marker used by the database engine to represent the absence of a value. It can appear in almost any column unless that column has a NOT NULL constraint. The key thing that makes NULL different from other values is that SQL uses three-valued logic: every condition evaluates to TRUE, FALSE, or UNKNOWN (which behaves like NULL). Any comparison involving NULL — even NULL = NULL — evaluates to UNKNOWN, not TRUE. This is why WHERE column = NULL never matches anything, no matter what is actually stored in the column.

This same rule cascades through the engine: arithmetic with NULL produces NULL (5 + NULL is NULL), string concatenation with NULL usually produces NULL, and most aggregate functions (SUM, AVG, COUNT(column), MAX, MIN) silently skip NULL values rather than treating them as zero. Understanding this behavior is essential — it explains a huge share of "why did my query return the wrong rows" bugs.

Syntax

There is no operator that creates a literal NULL value beyond writing the keyword itself. The syntax you use is almost always about testing for or substituting NULL:

Syntax	Purpose
column IS NULL	True only when the column’s value is missing
column IS NOT NULL	True only when the column has an actual value
COALESCE(expr1, expr2, ...)	Returns the first non-NULL expression in the list (standard SQL)
IFNULL(expr, replacement)	SQLite/MySQL shortcut for a two-argument COALESCE; SQL Server uses ISNULL()
column_name TYPE NOT NULL	Column constraint in CREATE TABLE that rejects NULL inserts
ORDER BY column NULLS FIRST | NULLS LAST	Explicitly controls where NULLs sort (supported in SQLite, PostgreSQL; MySQL requires a workaround)

Never use = NULL or <> NULL to test for a missing value — always use IS NULL / IS NOT NULL.

Examples

All examples below use this sample table:

CREATE TABLE employees_null (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    manager_id INTEGER,
    commission REAL
);

INSERT INTO employees_null (id, name, department, manager_id, commission) VALUES
    (1, 'Alice', 'Sales', NULL, 500.0),
    (2, 'Bob', 'Sales', 1, 250.0),
    (3, 'Carol', 'Engineering', 1, NULL),
    (4, 'Dave', 'Engineering', 1, NULL);

Alice is the top manager (no boss, so manager_id is NULL), and only the two Sales employees have a commission on file.

Example 1: Finding rows with IS NULL

SELECT name, commission
FROM employees_null
WHERE commission IS NULL;

name	commission
Carol	NULL
Dave	NULL

Only IS NULL correctly matches rows where commission was never set. Flipping it to IS NOT NULL would instead return Alice and Bob.

Example 2: NULL in arithmetic vs. COALESCE

SELECT name,
       commission,
       commission * 0.1 AS raw_bonus,
       COALESCE(commission, 0) * 0.1 AS safe_bonus
FROM employees_null
ORDER BY id;

name	commission	raw_bonus	safe_bonus
Alice	500.0	50.0	50.0
Bob	250.0	25.0	25.0
Carol	NULL	NULL	0.0
Dave	NULL	NULL	0.0

Multiplying NULL by anything produces NULL — that’s why raw_bonus is empty for Carol and Dave. Wrapping the column in COALESCE(commission, 0) substitutes 0 before the multiplication, giving a usable number instead.

Example 3: NULL in aggregates and GROUP BY

SELECT department,
       COUNT(*) AS total_employees,
       COUNT(commission) AS employees_with_commission,
       AVG(commission) AS avg_commission
FROM employees_null
GROUP BY department
ORDER BY department;

department	total_employees	employees_with_commission	avg_commission
Engineering	2	0	NULL
Sales	2	2	375.0

COUNT(*) counts rows regardless of content, while COUNT(commission) counts only the rows where that column is not NULL — that’s why Engineering shows 0 despite having two employees. AVG(commission) for Engineering is NULL because there are zero non-NULL values to average, not because the average is zero.

Example 4: NULL produced by a LEFT JOIN

SELECT e.name AS employee, m.name AS manager
FROM employees_null e
LEFT JOIN employees_null m ON e.manager_id = m.id
ORDER BY e.id;

employee	manager
Alice	NULL
Bob	Alice
Carol	Alice
Dave	Alice

This self-join looks up each employee’s manager by name. Alice’s manager_id is NULL, so the join condition e.manager_id = m.id can never match for her row — but because it’s a LEFT JOIN, her row is kept anyway with NULL filled in for the manager columns. This is one of the most common sources of NULL in query results: an outer join with no matching row on the other side.

How It Works Step by Step (Under the Hood)

The engine evaluates a query in logical order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and NULL affects several of these stages differently:

• WHERE: Any row where the condition evaluates to UNKNOWN is dropped, exactly like FALSE. This is why column = NULL filters out everything, including rows that actually contain NULL.
• JOIN: In a LEFT/RIGHT/FULL join, unmatched rows on the outer side get NULL filled in for every column from the non-matching table, as shown in Example 4.
• GROUP BY: SQL treats all NULL values as belonging to the same group, so rows with a NULL grouping key are grouped together rather than each forming its own group.
• Aggregate functions: COUNT(*) counts every row; every other aggregate (COUNT(col), SUM, AVG, MIN, MAX) silently ignores NULL inputs.
• ORDER BY: By default in SQLite, NULL is treated as smaller than any other value, so it sorts first in ascending order and last in descending order — unless you override this explicitly:

SELECT name, commission
FROM employees_null
ORDER BY commission ASC NULLS LAST, id ASC;

name	commission
Bob	250.0
Alice	500.0
Carol	NULL
Dave	NULL

The NULLS LAST clause forces the two NULL commissions to the bottom instead of the default top position. MySQL doesn’t support NULLS FIRST/LAST directly — the common workaround there is ORDER BY commission IS NULL, commission.

Common Mistakes

Mistake 1: Using = NULL instead of IS NULL

SELECT name FROM employees_null WHERE commission = NULL;

This looks reasonable but always returns zero rows, because commission = NULL evaluates to UNKNOWN for every row — even the ones where commission genuinely is NULL. Equality can never be used to test for "missing." The fix:

SELECT name FROM employees_null WHERE commission IS NULL;

This correctly returns Carol and Dave, because IS NULL is a dedicated null-test operator rather than a comparison.

Mistake 2: Forgetting that <> silently excludes NULL rows

SELECT name FROM employees_null WHERE manager_id <> 1;

The intent here is "show me everyone not managed by employee 1." Bob, Carol, and Dave all have manager_id = 1 so they’re correctly excluded — but Alice, whose manager_id is NULL, is also silently excluded, even though her manager clearly isn’t 1. NULL <> 1 evaluates to UNKNOWN, not TRUE, so this query actually returns zero rows. The fix is to explicitly include the NULL case:

SELECT name FROM employees_null WHERE manager_id <> 1 OR manager_id IS NULL;

This correctly returns Alice, the only employee not managed by employee 1.

Bonus: NOT NULL rejects the value at insert time

CREATE TABLE contacts (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
INSERT INTO contacts (id, email) VALUES (1, NULL);

Because email is declared NOT NULL, this INSERT is rejected by the engine with a constraint-violation error rather than silently storing an empty value. Declaring columns NOT NULL whenever a value is genuinely required is far safer than relying on application code to catch missing data later.

Best Practices

• Always use IS NULL / IS NOT NULL to test for missing values — never = or <>.
• When comparing a nullable column with <>, NOT IN, or similar, explicitly decide whether NULL rows should be included and add OR column IS NULL if so.
• Use COALESCE() (or your dialect’s shortcut like IFNULL()/ISNULL()) to supply a default value before doing arithmetic, concatenation, or display formatting.
• Declare columns NOT NULL whenever the business rule requires a value — it’s cheaper to reject bad data at insert time than to debug it later.
• Remember NOT IN (subquery) returns zero rows for the whole query if the subquery produces even one NULL — prefer NOT EXISTS when the subquery’s column can contain NULL.
• Use COUNT(column) instead of COUNT(*) when you specifically want to count non-missing values.
• Be explicit with NULLS FIRST/NULLS LAST in ORDER BY when NULL placement actually matters to your report, rather than relying on the engine’s default.

Practice Exercises

• Exercise 1: Using the employees_null table from this lesson, write a query that lists every employee’s name along with their commission, replacing any missing commission with the text 'No commission' instead of a number. (Hint: COALESCE only works cleanly when both arguments are the same type — think about casting or using CASE WHEN.)
• Exercise 2: Write a query that counts how many employees have no manager assigned (manager_id IS NULL) versus how many do. Expected result: 1 employee with no manager, 3 with a manager.
• Exercise 3: Explain in one sentence why SELECT * FROM employees_null WHERE manager_id NOT IN (SELECT manager_id FROM employees_null WHERE manager_id IS NOT NULL); behaves differently than you might expect if the subquery’s list happened to include a NULL, and rewrite it safely using NOT EXISTS.

Summary

• NULL means "unknown or missing," not zero or an empty string.
• SQL uses three-valued logic — comparisons involving NULL evaluate to UNKNOWN, which behaves like FALSE in WHERE clauses.
• Test for missing values with IS NULL / IS NOT NULL — never = or <>.
• Most aggregate functions ignore NULL values; COUNT(*) is the one exception that counts every row regardless.
• Outer joins (LEFT/RIGHT/FULL) fill unmatched columns with NULL — this is expected behavior, not an error.
• COALESCE() substitutes a default value for NULL, making arithmetic and display logic safer.
• A NOT NULL constraint enforces at the schema level that a column can never store a missing value.