SQL IS NULL / IS NOT NULL

NULL means "no value" — not zero, not an empty string, not false. You can never test for it with =; you need IS NULL or IS NOT NULL.

Syntax

WHERE column IS NULL
WHERE column IS NOT NULL

Sample Table: employees

Riya and Karan have no manager on record — manager_id is NULL, meaning "unknown / not set," not zero.

Example

SELECT name FROM employees
WHERE manager_id IS NULL;

Output: Riya, Karan.

Why WHERE manager_id = NULL Never Works

-- WRONG — always returns zero rows, no error
SELECT name FROM employees WHERE manager_id = NULL;

In SQL's three-valued logic, comparing anything to NULL with = — including NULL = NULL — evaluates to UNKNOWN, not TRUE. Rows are only returned when a condition is TRUE, so this silently returns nothing, with no error to warn you.

NULL and Aggregate Functions

COUNT(*) counts all rows regardless of NULLs, but COUNT(column) and functions like AVG(column) ignore NULL values in that column entirely — they don't treat NULL as zero. See COALESCE for substituting a default value.

Practical Use Case

Finding incomplete records — customers with no phone number, orders with no delivery date yet, employees with no assigned manager.

Common Mistakes

• Using = NULL or != NULL instead of IS NULL / IS NOT NULL
• Assuming an empty string '' is the same as NULL — they're different values and require different filters
• Forgetting NULL's effect inside NOT IN subqueries (see IN)

Interview Relevance

"Why doesn't WHERE column = NULL work?" is one of the most commonly asked SQL fundamentals questions — understanding three-valued logic (TRUE / FALSE / UNKNOWN) separates candidates who've memorized syntax from those who understand it.

Practice Question

Write a query to find all orders where the delivered_date column has not been set yet.

Related SQL Notes

• COALESCE
• NOT NULL Constraint
• WHERE Clause