SQL IS NULL

The literal value NULL in SQL represents “unknown” or “missing” — not zero, not an empty string, and not even equal to itself. Because ordinary comparison operators like = and <> can never resolve when one side is unknown, SQL gives you two special-purpose predicates, IS NULL and IS NOT NULL, that are the only reliable way to test whether a column actually holds a value. Get comfortable with them early: misunderstanding how NULL behaves is one of the most common sources of silently wrong query results in real-world SQL.

Overview: What NULL Really Means

In the relational model, a column can be in one of two states: it holds a concrete value, or it holds NULL, meaning the value is unknown, not applicable, or was never entered. A customer’s phone column might be NULL because they never provided one; an employee’s commission column might be NULL because that employee doesn’t earn commission at all. NULL is not the same as an empty string '' or the number 0 — both of those are actual, known values, whereas NULL represents the absence of a value entirely.

This distinction has a deep consequence for comparisons. SQL uses three-valued logic: every condition evaluates to TRUE, FALSE, or UNKNOWN. Any comparison involving NULL — NULL = 5, NULL <> 5, even NULL = NULL — evaluates to UNKNOWN, never TRUE or FALSE. A WHERE clause only keeps rows whose condition evaluates to TRUE, so a condition that evaluates to UNKNOWN silently drops the row without raising any error. That is exactly why WHERE column = NULL never works: it isn’t a syntax mistake the engine can catch, it simply never matches anything, ever.

IS NULL and IS NOT NULL exist to sidestep this problem entirely. They are not comparison operators — they are a dedicated null-check predicate that always evaluates to TRUE or FALSE, never UNKNOWN, no matter what is on the left-hand side. Internally, the query engine evaluates them by inspecting the column’s stored null indicator for that row rather than performing a value comparison, so the check is both logically correct and fast. This also means IS NULL can use a B-tree index the same way an equality lookup can — most engines, SQLite included, do store NULL entries in an index and can seek directly to them instead of scanning the whole table.

Syntax

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

SELECT column_list
FROM table_name
WHERE column_name IS NOT NULL;

Part	Meaning
column_name IS NULL	TRUE only when the column has no stored value for that row
column_name IS NOT NULL	TRUE only when the column holds a concrete, known value
IS	The required keyword for null-checking — never use = or <> against NULL
NOT	Optional, written between IS and NULL to negate the check

Notice this differs from every other predicate in SQL. With any other operator, negation wraps the whole condition, as in NOT (column = 5). With NULL checks, NOT sits in the middle of a fixed phrase: IS NOT NULL, not NOT column IS NULL (the latter also works, but is unusual style).

Examples

Example 1: Finding Rows With a Missing Value

A sales team tracks commission, but not every rep earns one. To find reps with no commission on record, test the column directly with IS NULL:

CREATE TABLE sales_reps (
  id INTEGER PRIMARY KEY,
  name TEXT,
  region TEXT,
  commission REAL
);

INSERT INTO sales_reps (id, name, region, commission) VALUES
  (1, 'Alice Chen', 'West', 1500.00),
  (2, 'Bob Martinez', 'East', NULL),
  (3, 'Carla Diaz', 'West', 800.50),
  (4, 'David Kim', 'North', NULL);

SELECT name, region
FROM sales_reps
WHERE commission IS NULL;

Result:

name	region
Bob Martinez	East
David Kim	North

Only Bob and David have no commission value stored, so they’re the only rows returned. Alice and Carla are excluded even though their commission values differ from each other, because the predicate only cares about presence versus absence of a value.

Example 2: Filtering to Rows That Do Have a Value

The opposite case is just as common: you often want to work only with rows where a column is actually filled in, such as sending an SMS only to customers who provided a phone number.

CREATE TABLE customer_contacts (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  phone TEXT,
  email TEXT
);

INSERT INTO customer_contacts (customer_id, customer_name, phone, email) VALUES
  (1, 'Priya Nair', '555-0101', 'priya@example.com'),
  (2, 'Tom O''Brien', NULL, 'tom@example.com'),
  (3, 'Yuki Tanaka', '555-0303', NULL);

SELECT customer_name, phone
FROM customer_contacts
WHERE phone IS NOT NULL;

Result:

customer_name	phone
Priya Nair	555-0101
Yuki Tanaka	555-0303

Tom O’Brien is skipped because his phone column is NULL, even though his email is filled in — each column’s null state is independent, so always check the specific column that matters for the task at hand.

Example 3: Combining IS NULL With Other Conditions and COALESCE

IS NULL combines naturally with other predicates using AND/OR, and pairs well with COALESCE(), which substitutes a default value when a column is NULL so reports display something readable instead of a blank.

CREATE TABLE inventory (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  stock INTEGER
);

INSERT INTO inventory (product_id, product_name, category, stock) VALUES
  (1, 'Wireless Mouse', 'Electronics', 42),
  (2, 'Desk Lamp', NULL, 15),
  (3, 'USB Cable', 'Electronics', 0),
  (4, 'Notebook', NULL, 100);

SELECT product_name,
       COALESCE(category, 'Uncategorized') AS category,
       stock
FROM inventory
WHERE category IS NULL OR stock = 0;

Result:

product_name	category	stock
Desk Lamp	Uncategorized	15
USB Cable	Electronics	0
Notebook	Uncategorized	100

The WHERE clause keeps any row missing a category OR out of stock, which is why USB Cable (a known category but zero stock) appears alongside the two uncategorized products. The COALESCE in the SELECT list only changes how NULL is displayed — it does not affect which rows are matched by the WHERE clause.

How SQL Evaluates NULL Comparisons (Under the Hood)

SQL processes a query in a fixed logical order — FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT — and NULL-handling matters at nearly every stage. During WHERE evaluation, the engine computes each row’s condition as TRUE, FALSE, or UNKNOWN and keeps only the TRUE rows; this is the mechanism that quietly drops rows when you compare against NULL incorrectly. During GROUP BY, all NULLs in a grouping column are treated as equal to each other and collected into a single group — this is a special-case exception to “NULL never equals NULL” that trips people up. Aggregate functions like COUNT(column), SUM(column), and AVG(column) silently skip NULL values in that column, which is why COUNT(*) (counts rows) and COUNT(column) (counts non-null values in that column) can return different numbers on the same table.

You can see three-valued logic directly by evaluating a few expressions with no table involved:

SELECT NULL = NULL   AS equals_null,
       NULL IS NULL  AS is_null_check,
       NULL <> NULL  AS not_equals_null;

Result:

equals_null	is_null_check	not_equals_null
NULL	1	NULL

Both = comparisons return NULL (SQLite’s representation of UNKNOWN), while IS NULL returns 1 (TRUE) because it is a dedicated predicate, not a value comparison.

Common Mistakes

Mistake 1: Comparing to NULL With = or <>

It’s tempting to treat NULL like any other literal and write = NULL. This does not raise an error, which makes it especially dangerous — it just quietly returns zero rows, every time, no matter what data exists.

-- Wrong: this can never match, even if some salaries really are NULL
SELECT *
FROM employees
WHERE salary = NULL;

Because salary = NULL always evaluates to UNKNOWN, no row is ever kept — even rows where salary genuinely is NULL. The fix is to use the dedicated predicate:

SELECT *
FROM employees
WHERE salary IS NULL;

This correctly returns every row where salary has no value, and returns an empty result set only when no such row actually exists.

Mistake 2: NULL Inside a NOT IN Subquery

This is one of the most common production bugs in SQL. If the list produced by a subquery contains even one NULL, NOT IN silently returns zero rows for the entire query — not just for the NULL entry.

CREATE TABLE staff (id INTEGER, name TEXT);
INSERT INTO staff VALUES (1, 'Ann'), (2, 'Ben'), (3, 'Cid');

CREATE TABLE excluded_ids (id INTEGER);
INSERT INTO excluded_ids VALUES (1), (NULL);

-- Wrong: NULL in the list makes every NOT IN comparison UNKNOWN
SELECT name
FROM staff
WHERE id NOT IN (SELECT id FROM excluded_ids);

You might expect Ben and Cid back, but you get zero rows. Internally, NOT IN (1, NULL) expands to id <> 1 AND id <> NULL; the second half is always UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies. Fix it by excluding NULLs from the subquery, or by using NOT EXISTS instead, which doesn’t have this trap:

CREATE TABLE staff (id INTEGER, name TEXT);
INSERT INTO staff VALUES (1, 'Ann'), (2, 'Ben'), (3, 'Cid');

CREATE TABLE excluded_ids (id INTEGER);
INSERT INTO excluded_ids VALUES (1), (NULL);

SELECT name
FROM staff
WHERE id NOT IN (SELECT id FROM excluded_ids WHERE id IS NOT NULL);

Filtering the subquery with WHERE id IS NOT NULL removes the poisoned NULL entry, and the query correctly returns Ben and Cid.

Best Practices

• Always use IS NULL / IS NOT NULL to test for missing values — never = or <>.
• Before writing a NOT IN (subquery), either add WHERE column IS NOT NULL inside the subquery or rewrite the query using NOT EXISTS.
• Remember that COUNT(column) excludes NULLs while COUNT(*) counts every row — pick the one that matches what you actually want to measure.
• Use COALESCE(column, default_value) when you need a display-friendly substitute for NULL, but don’t confuse it with filtering — it changes what’s shown, not which rows match.
• Design tables with NOT NULL constraints on columns that should always have a value, so missing data is caught at insert time instead of surfacing as a query bug later.
• When joining tables, remember that unmatched rows from an outer join produce NULLs in the columns of the non-matching side — test for those explicitly with IS NULL when you need to find “rows with no match.”

Practice Exercises

• Using a customers table and an orders table (like the ones referenced earlier in this course) joined with a LEFT JOIN, write a query that finds every customer who has never placed an order. Hint: after a LEFT JOIN, unmatched customers will have NULL in the order columns.
• Suppose an employees table has a manager_id column where NULL means that person has no manager (they’re a top-level executive). Write a query that lists the names of every executive.
• Given a survey_responses table with a rating column that is NULL whenever a respondent skipped the question, write one query using COUNT(*) and COUNT(rating) together to figure out how many responses were skipped, without writing a separate query for each count.

Summary

• NULL means “unknown” or “missing,” not zero or an empty string, and no value is ever equal to NULL — not even another NULL.
• SQL uses three-valued logic: comparisons return TRUE, FALSE, or UNKNOWN, and WHERE only keeps rows that evaluate to TRUE.
• IS NULL and IS NOT NULL are dedicated predicates, not comparison operators — they always resolve to TRUE or FALSE and are index-friendly.
• column = NULL and column <> NULL never match anything; this fails silently instead of raising an error.
• A single NULL inside a NOT IN subquery list can silently empty an entire result set — prefer NOT EXISTS or filter NULLs out first.
• Aggregate functions like COUNT(column), SUM(), and AVG() ignore NULLs in their target column by default.