IS NULL

Objectives

By the end of this lesson, you should be able to:

• Check for NULL correctly with IS NULL and IS NOT NULL
• Explain why = NULL never works

💡 Why this matters: Every comparison operator in this module so far has quietly skipped NULL values. This lesson covers the one check that actually finds them.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier in this module.

Why = NULL Doesn’t Work

NULL represents an unknown value (Module 3), and an unknown compared to anything, even another unknown, has no answer, so = NULL and != NULL are never true, for any row, ever:

SELECT first_name, department FROM employees WHERE department = NULL;

 first_name | department
------------+------------
(0 rows)

Even Devon, whose department genuinely is NULL, doesn’t show up here. This isn’t a bug, it’s exactly why IS NULL exists as its own syntax.

IS NULL and IS NOT NULL

SELECT first_name, department FROM employees WHERE department IS NULL;

 first_name | department
------------+------------
 Devon      |

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

 first_name | department
------------+-------------
 Erin       | Engineering
 Jordan     | Sales
 Maya       | Marketing
 Priya      | Engineering
 Sam        | Sales
 Taylor     | Engineering
 Alexis     | Marketing

IS NULL and IS NOT NULL are the only correct ways to check for NULL, in PostgreSQL or any other RDBMS. Every other comparison operator covered in this module, =, !=, IN, LIKE, silently excludes NULL rows from both a condition and its negation, exactly as seen with department != 'Engineering' two lessons ago.

Try It

1. Write a query for every employee whose department is NULL.
2. Write a query for every employee whose department is not NULL.
3. Run SELECT * FROM employees WHERE department = NULL; and confirm it returns zero rows, no matter what data is in the table, then explain why in your own words.

Recap

• NULL compared with = or != is never true, not even against another NULL.
• IS NULL and IS NOT NULL are the only correct checks for a missing value.
• Every comparison operator in this module quietly excludes NULL rows, from both a condition and its negation.

Next lesson: ORDER BY, controlling what order a query’s results come back in.