IS NULL

Definition

IS NULL tests whether a value is NULL — the only correct way to check for a missing/unknown value, since ordinary comparison operators (=, <>) can never produce TRUE when compared against NULL (Chapter 8's NULL Semantics, 12.4).

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name FROM products WHERE stock IS NULL;

This correctly finds every product whose stock hasn't been counted yet — Notebook, in the running example. stock = NULL would incorrectly return zero rows, always, regardless of how many products actually have a NULL stock.

Edge Cases and Pitfalls

• IS NULL is a special comparison operator PAIR (IS NULL/IS NOT NULL), not an ordinary function or a value comparison — it's genuinely part of SQL's syntax specifically because ordinary = cannot express this test.
• IS NULL can be combined with other conditions using AND/OR, exactly like any other condition: WHERE stock IS NULL OR stock = 0 finds products that are either uncounted OR confirmed empty.
• A common realistic use: LEFT JOIN ... WHERE right_table.key IS NULL — the anti-join pattern (used repeatedly in this course, e.g. 5.11, 7.11) for finding rows with NO match in another table.

Key Takeaways

• IS NULL is the only correct way to test for a missing value — never use = NULL.
• It's a distinct SQL syntax construct, not an ordinary comparison operator applied to the literal NULL.
• Combined with LEFT JOIN, IS NULL is the standard anti-join pattern for finding unmatched rows.