NULL: no value

Problem

The customer who vanished

Marketing wants to write to every customer outside Sofia. There are 20 customers, 6 of them from Sofia. You expect 14:

SQLdatabase: shop

SELECT name, city FROM customers WHERE city <> 'Sofia';

SELECT name, city FROM customers WHERE city <> 'Sofia';

▶ RunRestore the queryFresh database

13 come out. Who is missing? Boris Mitev, who never said which city he is from. He is not from Sofia, yet he is not in the result.

The idea

Unknown

Boris's city is NULL: the absence of a value. It is not empty text and not zero. It means “we do not know”.

That is why SQL works with three answers: true, false and unknown. “Is Boris's city different from Sofia?” We do not know, his city is unknown. The answer is “unknown”, and WHERE keeps only the rows for which it is true.

The same happens when you look for NULL with =:

SQLdatabase: shop

SELECT name, city FROM customers WHERE city = NULL;

SELECT name, city FROM customers WHERE city = NULL;

▶ RunRestore the queryFresh database

Nothing. NULL = NULL is also “unknown”: two unknown values may be equal, or may not.

The idea

IS NULL

NULL has its own check: IS NULL, and the opposite IS NOT NULL.

SQLdatabase: shop

SELECT name, city FROM customers WHERE city IS NULL;

SELECT name, city FROM customers WHERE city IS NULL;

▶ RunRestore the queryFresh database

It is the only correct way. = NULL is a mistake that gives no message, just an empty result.

Try it

No category

Show the name and price of the products that have no category. The field already has an attempt. Run it and fix it.

SQLdatabase: shop

SELECT name, price FROM products WHERE category_id = NULL;

SELECT name, price FROM products WHERE category_id = NULL;

Check▶ Run

Try it

Everyone outside Sofia

Fix the query from the start of the lesson: every customer outside Sofia, including those without a city.

SQLdatabase: shop

SELECT name, city FROM customers WHERE city <> 'Sofia';

SELECT name, city FROM customers WHERE city <> 'Sofia';

Check▶ Run

See it

NOT IN and one NULL

A NOT IN list with a NULL in it returns an empty result:

SQLdatabase: shop

SELECT name, city FROM customers
WHERE city NOT IN ('Sofia', NULL);

SELECT name, city FROM customers WHERE city NOT IN ('Sofia', NULL);

▶ RunRestore the queryFresh database

Why? city NOT IN ('Sofia', NULL) means city <> 'Sofia' AND city <> NULL. The second part is always “unknown”, so the whole condition is never true.

You will rarely write NULL in a list by hand. But when the list comes from another query and has a NULL in it, you get an empty report with no error at all. We will meet it again with subqueries.

Exercise

The products in a category

Show the name and category of all products that have a category.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. What is NULL?

The number 0The absence of a value: unknown or noneEmpty text

2. What is the result of NULL = NULL?

FalseUnknown (NULL)True

3. Which one finds the products without a category?

WHERE category_id = NULLWHERE category_id = ''WHERE category_id IS NULL

Check

Start the lesson over