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.