NULL: Missing Values

NULL means a value is missing or unknown. It isn't zero, and it isn't an empty string — it's the absence of a value. In our sample data, Tim Berners-Lee's city is NULL.

You can't compare with = NULL

SELECT name, city FROM customers WHERE city = NULL;

This returns no rows at all — not even Tim's. Any comparison with NULL gives unknown, not true — "is an unknown city equal to an unknown value?" can't be answered. Use IS NULL and IS NOT NULL:

SELECT name, city FROM customers WHERE city IS NULL;

+-----------------+------+
|      name       | city |
+-----------------+------+
| Tim Berners-Lee | NULL |
+-----------------+------+

NULL can hide rows from filters

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

+-------------------+----------+
|       name        |   city   |
+-------------------+----------+
| Linus Torvalds    | Helsinki |
| Grace Hopper      | New York |
| Margaret Hamilton | Boston   |
+-------------------+----------+

Tim doesn't appear: his city isn't known to differ from London, so the condition isn't true. To include missing values, say so: WHERE city <> 'London' OR city IS NULL.

NULL in calculations

Arithmetic with NULL gives NULL:

SELECT 10 + NULL AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

COALESCE: supply a default

COALESCE returns the first value that isn't NULL:

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers;

+-------------------+----------+
|       name        |   city   |
+-------------------+----------+
| Ada Lovelace      | London   |
| Linus Torvalds    | Helsinki |
| Grace Hopper      | New York |
| Alan Turing       | London   |
| Margaret Hamilton | Boston   |
| Tim Berners-Lee   | Unknown  |
+-------------------+----------+

COALESCE is standard SQL and works everywhere. (You'll also see IFNULL in MySQL and SQLite, and ISNULL in SQL Server.)

Common mistakes

• Writing = NULL or <> NULL. Always use IS NULL / IS NOT NULL.
• Forgetting that NOT IN (...) returns no rows if the list contains a NULL — a classic trap with subqueries (Level 2).

Practice

List the customers who do have a city, showing their name and city.

SELECT name, city
FROM customers
WHERE city IS NOT NULL;

+-------------------+----------+
|       name        |   city   |
+-------------------+----------+
| Ada Lovelace      | London   |
| Linus Torvalds    | Helsinki |
| Grace Hopper      | New York |
| Alan Turing       | London   |
| Margaret Hamilton | Boston   |
+-------------------+----------+