Why can't I just write WHERE manager_id = NULL?

Because NULL represents an unknown value, and SQL has no way to know whether one unknown value equals another. Any comparison using = with NULL evaluates to unknown, not true, so the row is never returned. You must use IS NULL or IS NOT NULL instead.

Is NULL the same as zero or an empty string?

No. Zero is a real number and an empty string is a real (if empty) piece of text. NULL means the value is absent or unknown entirely, there's a meaningful difference between a salary of 0 and a salary that was never recorded.

beginner

Understanding NULL

What NULL means in SQL, how it differs from zero or an empty string, and how to test for it correctly.

5 min read

Explanation

Sometimes a column simply has no value for a given row. Maybe the data was never collected, doesn't apply, or hasn't been entered yet. SQL represents this absence with a special marker called NULL.

In the employees table, most employees have a manager_id pointing to another employee. But someone at the top of the org chart, like a CEO, has no manager at all. That employee's manager_id is NULL, not zero, and not any employee's real id.

id	first_name	manager_id
1	Alice	NULL
2	Brian	1
3	Carla	1

NULL isn't a value like 0 or "", it means "unknown" or "not applicable." This has a big consequence: you can't compare against NULL using the usual = operator.

= NULL never works

WHERE manager_id = NULL will never match any rows, even rows where manager_id really is NULL. Since NULL means "unknown," SQL can't determine whether an unknown value equals another unknown value, so the comparison is itself unknown, and unknown is treated as false.

Syntax

To correctly test for NULL, use the dedicated IS NULL and IS NOT NULL operators:

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

You'll also encounter NULL when you want to substitute a default value instead of showing a blank. Later in this course, you'll meet the COALESCE function, which lets you replace NULL with a fallback value, for example showing "No Manager" instead of a blank cell. For now, just know that IS NULL and IS NOT NULL are the tools for filtering.

Interactive Example

Employees & Departments

Loading database engine...

Employees & Departments

Loading database engine...

Common Mistakes

Using = NULL or != NULL. Both silently return zero rows instead of raising an error, which makes this mistake easy to miss.
Assuming NULL means zero. A NULL salary is not the same as a salary of 0. One means "no data," the other means "the value is zero."
Forgetting NULL can appear in any column. Even columns that are usually filled in, like email or department_id, can contain NULL if the data is incomplete.

Best Practices

Always use IS NULL or IS NOT NULL when checking for missing values, never = or !=.
When designing or reading a schema, ask whether a column is allowed to be NULL and what that would mean for your query logic.
Be careful with NOT IN when the list might contain NULL, it can produce surprising empty results. This becomes more relevant once you start writing subqueries later in the course.