Look at customer 10 in the customers tab. The email cell is empty, marked NULL.
customers
| id | name | city | |
|---|---|---|---|
| 9 | Yuki Tanaka | yuki.t@example.com | Osaka |
| 10 | Hannah Weber | NULL | Berlin |
| 11 | Diego Lopez | diego.l@example.com | Monterrey |
| 12 | Grace Okafor | grace.o@example.com | NULL |
means "no value here". It is not 0, and it is not empty text ''.
= NULL never works
SELECT name FROM customers WHERE email = NULL;This returns no rows. The database cannot say whether an unknown value is equal to something, so the comparison is not TRUE and not FALSE. It is UNKNOWN.
Use IS NULL and IS NOT NULL:
| Condition | Keeps the rows where |
|---|---|
email IS NULL | the email is missing |
email IS NOT NULL | the email has a value |
TRUE, FALSE and UNKNOWN
SQL conditions have three results. WHERE keeps a row only when the result is TRUE. It removes FALSE and UNKNOWN rows.
Every comparison with NULL gives UNKNOWN: NULL = 'x', NULL <> 'x', NULL > 5, even NULL = NULL. So WHERE city <> 'Toronto' removes Toronto, and it also removes Grace Okafor, whose city is NULL.
AND, OR and NOT with UNKNOWN:
| a | b | a AND b | a OR b |
|---|---|---|---|
| TRUE | UNKNOWN | UNKNOWN | TRUE |
| FALSE | UNKNOWN | FALSE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
NOT UNKNOWN is UNKNOWN. A rule to remember: when the unknown value could change the answer, the answer is UNKNOWN.