Lessons · SQL · testing for NULL

Finding the blanks

IS NULL finds rows where a column has no value; IS NOT NULL finds the rest. They are the only operators that can see NULL.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Which customers have not given an email, which orders lack a date, which products have no price: the missing-data report is the first cleanup job on any table.

How to think about it

For each column that can be empty, count the blanks with COUNT(*) FILTER-style logic or a WHERE IS NULL, and decide whether a blank is allowed, a bug, or a default waiting to be filled.

Worked example

SELECT COUNT(*) FROM customers WHERE email IS NULL;

How many are missing an email.

SELECT name FROM customers WHERE email IS NULL ORDER BY name;

Who they are.

SELECT name FROM customers WHERE email IS NOT NULL;

The ones you can write to.

Your turn

Products with a price.

SELECT name FROM products WHERE price ;

The trap

An empty string is not NULL. A column full of '' passes IS NOT NULL and still has no data; check both when the source is a form.

Practise testing for NULL on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.