CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 3 of 5 · 1 min read

Testing for NULL correctly

IS NULL and IS NOT NULL, the only correct way to test for NULL.

Think of asking directly "is this field blank," rather than trying to compare the blank field to some specific value.

In plain words: IS NULL tests whether a value is NULL, returning true or false directly, never unknown. IS NOT NULL tests the opposite. These are the only reliable way to check for NULL, since ordinary comparison operators cannot.

A worked example. Finding every book with no recorded discount.

The key lines.

SELECT * FROM books WHERE discount IS NULL;

IS NULL, IS NOT NULL

IS NULL tests whether a value is NULL, returning a plain true or false. IS NOT NULL tests the opposite.

Example: WHERE discount IS NULL correctly finds books with no recorded discount, unlike WHERE discount = NULL, which finds none.