IS NULL, IS NOT NULL and NULL-Safe Equality
Correctly testing for NULL and the NULL-safe operators per dialect.
Testing for NULL the Right Way
The previous lesson proved you cannot use = to find NULLs. So how do you actually test for them? With the dedicated predicates IS NULL and IS NOT NULL.
These are the only correct, portable way to check for missing values, and interviewers will reject col = NULL every time they see it.
This lesson covers IS NULL, IS NOT NULL, the IS DISTINCT FROM family, and the dialect-specific NULL-safe equality operators. Knowing the cross-database differences is a strong senior signal.
IS NULL and IS NOT NULL
IS NULL returns TRUE when the value is NULL and FALSE otherwise. Crucially, it never returns UNKNOWN, so it is safe to use directly in WHERE.
IS NOT NULL is its exact complement: TRUE for any actual value, FALSE for NULL.
These predicates are the workhorses of NULL handling. They are standard SQL and behave identically across MySQL, Postgres, SQL Server, Oracle, and SQLite.
-- Find employees with no recorded bonus
SELECT name FROM employees WHERE bonus IS NULL;
-- Find employees that do have a bonus
SELECT name FROM employees WHERE bonus IS NOT NULL;All lessons in this course
- Three-Valued Logic and UNKNOWN
- IS NULL, IS NOT NULL and NULL-Safe Equality
- COALESCE, NULLIF and ISNULL
- NULLs in Aggregates, Joins and DISTINCT