NULL Values and IS NULL
What you'll learn: How to identify and filter missing or unknown data in SQL databases using NULL and the IS NULL operator.
What is NULL?
In SQL, NULL represents missing, unknown, or undefined data—it's not zero, not an empty string, just absence of a value. Think of a form where someone skipped the "middle name" field: that slot isn't filled with anything; it's blank by intent or lack of information.
Because NULL means "unknown," it behaves differently than normal values. You can't compare it using regular operators like = or !=. After all, how can you say something equals "unknown"?
Testing for NULL: IS NULL and IS NOT NULL
To check whether a column contains NULL, SQL provides special operators:
IS NULL– returns true if the value is NULLIS NOT NULL– returns true if the value is not NULL
Example:
SELECT * FROM employees
WHERE middle_name IS NULL;
This finds all employees who have no middle name recorded.
SELECT * FROM employees
WHERE email IS NOT NULL;
This retrieves employees who do have an email address on file.
Why Not Use = NULL?
Because NULL represents "unknown," comparing anything to it—even another NULL—yields unknown (treated as false in SQL). So WHERE middle_name = NULL won't work; you must use IS NULL.
Key Takeaway:
Use IS NULL and IS NOT NULL to test for missing data in SQL; regular comparison operators like = won't work with NULL values because NULL represents "unknown."