NULL has come up a few times already in this course, in a FAQ here, a note there. This lesson covers it properly: what NULL actually means, why it breaks the comparisons you'd normally expect, and how to handle it on purpose instead of getting tripped up by it.
Learning Objectives
- Explain what NULL represents, and why it's not the same as zero or an empty string.
- Use IS NULL / IS NOT NULL correctly, and understand why = NULL never works.
- Substitute a default value for NULL with COALESCE.
Missing Value
No phone number on file
Stored as NULL
Not 0, not ""
Queried with IS NULL
Never with = NULL
NULL Means "Unknown," Not "Empty"
A text column holding '' (an empty string) has a known value, it's just empty. A numeric column holding 0 has a known value, it's just zero. NULL is different: it means the value is unknown, missing, or not applicable, a middle_name column that's NULL means that person simply has no middle name on record, not that their middle name is an empty string.
| Value | What it means |
|---|---|
| 0 | The number zero, a known value |
| '' (empty string) | Text that's present but empty, a known value |
| NULL | No value is recorded at all, unknown |
Why WHERE column = NULL Never Works
In SQL, comparing anything to an unknown value produces an unknown result, not true or false. salary = NULL doesn't evaluate to false, it evaluates to unknown, and a WHERE clause only keeps rows where the condition is true, so unknown rows are silently excluded. IS NULL and IS NOT NULL are special operators built specifically to test for NULL correctly.
SELECT * FROM employees WHERE middle_name = NULL;
SELECT * FROM employees WHERE middle_name IS NULL;
NULL in Aggregate Functions
Every aggregate function except COUNT(*) ignores NULL values entirely. AVG(bonus) averages only the rows that have a bonus, it doesn't treat a missing bonus as 0, which would pull the average down incorrectly.
SELECT COUNT(*) AS total_rows, COUNT(bonus) AS rows_with_bonus FROM employees; -- COUNT(*) counts every row. -- COUNT(bonus) only counts rows where bonus is NOT NULL.
Substituting a Default With COALESCE
COALESCE returns the first non-NULL value from a list of arguments, here, the actual bonus if there is one, otherwise 0. It's standard SQL and works identically across every database. MySQL's IFNULL and SQL Server's ISNULL do the same two-argument job but aren't portable to other databases, COALESCE is the safer default habit.
SELECT first_name, COALESCE(bonus, 0) AS bonus FROM employees;
SELECT first_name, IFNULL(bonus, 0) AS bonus FROM employees;
SELECT first_name, ISNULL(bonus, 0) AS bonus FROM employees;
NULL and Sorting
| Database | Default position of NULL in ORDER BY ASC |
|---|---|
| MySQL / SQLite | First (treated as the lowest possible value) |
| PostgreSQL / SQL Server | Last (treated as the highest possible value) |
SELECT first_name, bonus FROM employees ORDER BY bonus ASC NULLS LAST;
Where NULL sorts by default genuinely differs by database. PostgreSQL is the only one of the four with explicit NULLS FIRST / NULLS LAST syntax to control it directly, on the others, filter out NULLs with WHERE column IS NOT NULL first if their default position isn't what you want.
Frequently Asked Questions
Is NULL = NULL true?
No, it's unknown, the same as comparing any value to NULL. Two unknowns aren't guaranteed to be equal just because they're both unknown. Use IS NULL on both sides if you need to check that two columns are both NULL.
Can a primary key column be NULL?
No, every database enforces NOT NULL automatically on primary key columns, a primary key exists specifically to uniquely identify a row, and NULL can't do that reliably.
Does CONCAT with a NULL argument return NULL?
It depends on the database. In PostgreSQL and SQL Server, 'Hello ' || NULL (or CONCAT with the + operator in SQL Server) returns NULL, any NULL "poisons" the result. MySQL's CONCAT() function also returns NULL if any argument is NULL, but MySQL's CONCAT_WS() skips NULL arguments instead. Always check your specific database's behavior here.
Common Beginner Mistakes
Writing WHERE column = NULL instead of WHERE column IS NULL
This returns zero rows every time, with no error to warn you. It's one of the most common SQL mistakes precisely because it looks correct and fails silently.
Treating NULL and an empty string as the same thing
They're different concepts: '' is a known, empty value, NULL is an unknown or missing value. WHERE column = '' and WHERE column IS NULL match different rows, don't use them interchangeably.
Assuming AVG() includes NULL rows as zero
It doesn't, NULL rows are excluded from the average entirely, not counted as 0. If you actually want missing values treated as 0 in an average, wrap the column in COALESCE(column, 0) first.
Best Practices
- Always use IS NULL / IS NOT NULL to test for NULL, never = or <>.
- Reach for COALESCE over database-specific functions like IFNULL or ISNULL, for SQL that stays portable across databases.
- Be deliberate about whether a column should allow NULL at all, a NOT NULL constraint with a sensible default is often clearer than permitting NULL and handling it everywhere it's read.
- Check your specific database's documentation for how NULL behaves in string concatenation and sorting, since both genuinely vary by database.
Interview Questions
Why does WHERE salary != NULL never match any rows, even ones where salary genuinely isn't NULL?
Any comparison involving NULL, including !=, evaluates to unknown rather than true or false, and WHERE only keeps rows where the condition evaluates to true. This applies regardless of what the other side of the comparison is, the fix is always IS NULL or IS NOT NULL.
Write a query that shows each employee's bonus, defaulting to 0 if they don't have one, ordered highest to lowest.
SELECT first_name, COALESCE(bonus, 0) AS bonus FROM employees ORDER BY bonus DESC; — COALESCE handles the default, and ORDER BY sorts on the resulting value.
What's the difference between COUNT(*) and COUNT(column_name)?
COUNT(*) counts every row in the result set, regardless of NULLs. COUNT(column_name) counts only the rows where that specific column is NOT NULL. They can return different numbers on the same table if that column has any missing values.
Summary
This lesson covered what NULL actually represents, why IS NULL is required instead of = NULL, how aggregate functions and sorting treat NULL differently across databases, and how to substitute a default value with COALESCE.
What's Next?
The next lesson covers aggregate functions and GROUP BY in depth, summarizing data across many rows at once.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.