Missing Values

Terms defined: null, ternary logic

select
    flipper_length_mm / 10.0 as flipper_cm,
    body_mass_g / 1000.0 as weight_kg,
    island as where_found
from penguins
limit 5;

| flipper_cm | weight_kg | where_found |
|------------|-----------|-------------|
| 18.1       | 3.75      | Torgersen   |
| 18.6       | 3.8       | Torgersen   |
| 19.5       | 3.25      | Torgersen   |
|            |           | Torgersen   |
| 19.3       | 3.45      | Torgersen   |

SQL uses a special value null to representing missing data
- Not 0 or empty string, but "I don't know"
Flipper length and body weight not known for one of the first five penguins
"I don't know" divided by 10 or 1000 is "I don't know"

Exercise

Use SQLite's .nullvalue command to change the printed representation of null to the string null and then re-run the previous query. When will displaying null as null be easier to understand? When might it be misleading?

Null Equality

Repeated from earlier

select distinct
    species,
    sex,
    island
from penguins
where island = 'Biscoe';

| species |  sex   | island |
|---------|--------|--------|
| Adelie  | FEMALE | Biscoe |
| Adelie  | MALE   | Biscoe |
| Gentoo  | FEMALE | Biscoe |
| Gentoo  | MALE   | Biscoe |
| Gentoo  |        | Biscoe |

If we ask for female penguins the row with the missing sex drops out

select distinct
    species,
    sex,
    island
from penguins
where island = 'Biscoe' and sex = 'FEMALE';

| species |  sex   | island |
|---------|--------|--------|
| Adelie  | FEMALE | Biscoe |
| Gentoo  | FEMALE | Biscoe |

Null Inequality

But if we ask for penguins that aren't female it drops out as well

select distinct
    species,
    sex,
    island
from penguins
where island = 'Biscoe' and sex != 'FEMALE';

| species | sex  | island |
|---------|------|--------|
| Adelie  | MALE | Biscoe |
| Gentoo  | MALE | Biscoe |

Ternary Logic

select null = null;

| null = null |
|-------------|
|             |

If we don't know the left and right values, we don't know if they're equal or not
So the result is null
Get the same answer for null != null
Ternary logic

equality
	X	Y	null
X	true	false	null
Y	false	true	null
null	null	null	null

Handling Null Safely

select
    species,
    sex,
    island
from penguins
where sex is null;

| species | sex |  island   |
|---------|-----|-----------|
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Torgersen |
| Adelie  |     | Dream     |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |
| Gentoo  |     | Biscoe    |

Use is null and is not null to handle null safely
Other parts of SQL handle nulls specially

Exercise

Write a query to find penguins whose body mass is known but whose sex is not.
Write another query to find penguins whose sex is known but whose body mass is not.

Check Understanding

Figure 1: Missing Value Concepts