IS NOT NULL

Definition

IS NOT NULL tests whether a value is present (not NULL) — the direct complement of IS NULL (12.10), and equally necessary since <> NULL suffers the exact same "never TRUE" problem as = NULL.

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

sql
SELECT name, stock FROM products WHERE stock IS NOT NULL;

This correctly returns every product whose stock HAS actually been counted — excluding Notebook. Just like IS NULL, this is dedicated syntax, not a comparison against the literal value NULL.

Edge Cases and Pitfalls

• NOT (column IS NULL) and column IS NOT NULL are equivalent — but the direct IS NOT NULL form is clearer and more idiomatic than wrapping IS NULL in NOT.
• A NOT NULL constraint on a column (Chapter 10) means IS NOT NULL will ALWAYS be true for every row in that column — the runtime check becomes redundant specifically for that column, though it still matters for any nullable column, or after an outer join potentially reintroduces NULLs even for an originally NOT NULL column from the non-preserved side.
• Combining IS NOT NULL with other filters is common for "only consider complete records": WHERE email IS NOT NULL AND phone IS NOT NULL finds only fully-contactable customers.

Key Takeaways

• IS NOT NULL correctly tests for a present (non-NULL) value; <> NULL does not work, for the same reason = NULL doesn't.
• It's the direct, idiomatic complement to IS NULL — prefer it over NOT (... IS NULL).
• Especially useful for filtering to "complete" records across several optionally-NULL columns.