SQL NULL Values

A column can be left without a value, a middle_name no one provided, a bonus that doesn't apply this year. SQL represents that absence with NULL, a special marker that behaves differently from any ordinary value, covered here, and in more depth later in this course once more operators are available to show it with.

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.
Understand why WHERE column = NULL never works.

Column Checked

middle_name

No Value Recorded

Not 0, not ""

IS NULL

The only correct way to test it

NULL Means "Unknown," Not "Empty"

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

Checking for NULL

Comparing anything to NULL, including with =, produces an unknown result, not true or false, so a WHERE clause using = NULL silently matches nothing. IS NULL and IS NOT NULL are special operators built specifically to test for NULL correctly.

This returns zero rows, always

SELECT * FROM employees WHERE middle_name = NULL;

Correct way to check for NULL

SELECT * FROM employees WHERE middle_name IS NULL;
SELECT * FROM employees WHERE middle_name IS NOT NULL;

Try It Yourself

Write a query that finds every employee who does have a middle_name on record.

Answer

SELECT first_name, middle_name
FROM employees
WHERE middle_name IS NOT NULL;

Frequently Asked Questions

Is NULL = NULL true?

No, it's unknown, the same as comparing any value to NULL. 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, covered later in this course, a primary key exists specifically to uniquely identify a row.

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: '' is a known, empty value, NULL is an unknown or missing value. WHERE column = '' and WHERE column IS NULL match different rows.

Best Practices

Always use IS NULL / IS NOT NULL to test for NULL, never = or <>.
Be deliberate about whether a column should allow NULL at all, covered in the Constraints module later in this course.

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. The fix is always IS NULL or IS NOT NULL.

Write a query finding employees who have no recorded bonus.

SELECT first_name FROM employees WHERE bonus IS NULL; — IS NULL is the only correct way to test for a missing value.

Summary

This lesson covered what NULL actually represents, and why IS NULL / IS NOT NULL are required instead of = NULL.

What's Next?

The next lesson covers UPDATE, changing values in rows that already exist.

</>

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.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.