SQL IS NULL and IS NOT NULL Explained

NULL in SQL represents a missing or unknown value — not zero, not an empty string, and not "false." It simply means "we don't have a value here." Because of this special meaning, NULL can't be tested with the ordinary = operator, which trips up almost everyone the first time they hit it.

SQL gives you two dedicated keywords for this: IS NULL and IS NOT NULL.

Why = NULL Doesn't Work

In SQL's three-valued logic, any comparison against NULL — including NULL = NULL — evaluates to UNKNOWN, not TRUE. A WHERE clause only keeps rows where the condition is TRUE, so a query like this silently returns nothing, even if rows with a missing value exist:

-- This returns ZERO rows, even if some employees have no department set
SELECT name FROM employees WHERE department = NULL;

The Correct Syntax: IS NULL / IS NOT NULL

-- Employees with no department assigned
SELECT name, department
FROM employees
WHERE department IS NULL;

Result:

name | department
--------+-----------
Priya | NULL

-- Employees who DO have a department
SELECT name, department
FROM employees
WHERE department IS NOT NULL;

NULL in Calculations and Conditions

NULL is contagious in expressions: salary + NULL is NULL, and NULL AND TRUE is still not simply TRUE. If you need a fallback value in place of a NULL, most databases provide a COALESCE() function (or vendor-specific equivalents like IFNULL()/ISNULL()):

-- Show "Unassigned" instead of a blank department
SELECT name, COALESCE(department, 'Unassigned') AS department
FROM employees;

Result:

name | department
--------+-----------
Priya | Unassigned
Aisha | Marketing

Understanding NULL properly also matters once you start writing joins — an unmatched row in an outer join produces NULLs for the missing side, and this same IS NULL check is exactly how you'd filter for "rows with no match" in that scenario.