Fulfledge

Pull rows out, then filter them

INPUT · Slides

Looking for what is empty

01 / 05

Some values are not in yet

The timing got missed, the note never got written. A table will have squares with nothing in them yet.

What sits in such a square is NULL. It is a special mark that means "there is no value here".

SELECT * FROM records;

Result

1 | Minami | sprint | 42 | personal best
2 | Sota | jump | 38 |
3 | Himari | throw |  | injured
4 | Kaede | sprint | 45 |
5 | Riku | jump |  | watching
6 | Aoi | throw | 51 | personal best

02 / 05

NULL is neither 0 nor an empty string

0 means "the value is zero". An empty string means "the text is nought characters long". Both of them are values.

NULL is not like that. There is no value at all. The split exists so that "a time nobody measured" and "a time of zero" do not get mixed up.

03 / 05

= NULL finds nothing

Here is the trap. Write score = NULL and not one row comes back.

NULL means "unknown", so if you ask whether something equals an unknown, there is no answer to give. And no error either — it just quietly returns nothing, which is the frightening part.

SELECT * FROM records  WHERE score = NULL;

Result

(nothing comes back)

04 / 05

Ask with IS NULL

The writing made for asking whether something is empty is IS NULL. It asks "is this in the state of having no value?"

The point is that it is IS, not =. That pulled out exactly the rows where the time never got taken.

SELECT * FROM records  WHERE score IS NULL;

Result

3 | Himari | throw |  | injured
5 | Riku | jump |  | watching

05 / 05

IS NOT NULL for the other way

When you want the rows that do have a value, it is IS NOT NULL — a NOT slipped between IS and NULL.

You will often use it to drop the empty rows before you add anything up. Let us write some.

SELECT member, score FROM records  WHERE score IS NOT NULL;

Result

Minami | 42
Sota | 38
Kaede | 45
Aoi | 51