Databases & SQL · Class 12 · Database Concepts

databasesNULL⏱️ 8 min read

NULL — the Missing Value

Look back at the students table and you will see the word NULL sitting in four cells. It is not a name, not a number and not a mistake. It is how a database writes down “nobody has filled this in” — and it behaves unlike any other value in SQL.

1Why a special value is needed

Two students have no percentage recorded and two have no city. Perhaps they joined late, perhaps the form was incomplete, perhaps the exam has not happened yet.

MySQL command line client

mysql> select id, name, percent, city from students;

+------+--------+---------+----------+

+------+--------+---------+----------+

| 1099 | Veena | 90.5 | Singtam |

| 1187 | Anjali | 91 | Gangtok |

| 1250 | Sohan | 66 | NULL |

| 1402 | Lhamu | 95.2 | Namchi |

| 2035 | Ravi | 83.75 | Gangtok |

| 2044 | Karma | 78.5 | Singtam |

| 2199 | Diana | 88.25 | Kolkata |

| 3033 | Tom | 64.25 | Kolkata |

| 3301 | Farhan | 72.8 | Siliguri |

| 3612 | Nima | 59.4 | NULL |

+------+--------+---------+----------+

12 rows in set (0.00 sec)

The cell cannot simply be left blank — a table is a rigid grid, and every row has to have something in every column. So the database puts a marker there instead.

NULL means the value is missing, unknown, or not applicable.

2NULL is not zero, and not blank

This is the whole lesson, and it is worth being stubborn about. Bhim has NULL in percent. That does not mean he scored 0.

A definite value

“He sat the exam and scored nothing.” A real, known result.

An empty string

A piece of text that happens to have no characters. Still a value somebody entered.

NULL

No value at all

Nothing was ever entered. The database does not know what belongs here.

MySQL agrees. Asking for the students whose percentage is 0, or is an empty string, finds nobody — the two NULL rows are not matched by either:

MySQL command line client

mysql> select * from students where percent = 0;

Empty set (0.00 sec)

mysql> select * from students where percent = "";

Empty set (0.00 sec)

Why it matters for marks

Suppose you average the percent column. If the two missing values were stored as 0, the average would be dragged down and would be wrong. Stored as NULL they are left out of the calculation altogether, and the average is the average of the students who actually have a result. That is the practical reason the distinction exists.

3NULL spreads through a calculation

If you do not know a number, you cannot know what it plus five is either. SQL says so honestly:

MySQL command line client

mysql> select 5 + null;

+----------+

| 5 + null |

+----------+

| NULL |

+----------+

1 row in set (0.00 sec)

Anything combined with an unknown is itself unknown. This is worth remembering the first time an arithmetic result comes back as NULL for no obvious reason — one of the values going in was missing.

4Comparing with NULL is the trap

Here is the surprising part. Comparing anything to NULL does not give true or false. It gives NULL:

MySQL command line client

mysql> select null = null;

+-------------+

| null = null |

+-------------+

| NULL |

+-------------+

1 row in set (0.00 sec)

mysql> select null = 0;

+----------+

| null = 0 |

+----------+

| NULL |

+----------+

1 row in set (0.00 sec)

mysql> select null <> null;

+--------------+

| null <> null |

+--------------+

| NULL |

+--------------+

1 row in set (0.00 sec)

Read the first one carefully, because it looks wrong. null = null is not true. Two students whose city is unknown are not thereby from the same city — you have no idea whether they are. Unknown compared with unknown is still unknown.

This is why = NULL never works

A row is only returned when its condition comes out true. percent = NULL is never true — it is NULL — so the query silently returns nothing at all. No error, no warning, just an empty result, which is the worst kind of bug to spot.

SQL therefore gives you a separate pair of operators for the job, IS NULL and IS NOT NULL. They get a lesson of their own in the SELECT chapter; for now, just remember that = is the wrong tool for a missing value.

5Where NULL is and is not allowed

NULL and the keys you have met

Column	Can it hold NULL?	Why
Primary key	No, never	A row with no identifier could not be identified — that is the whole job of the key
An ordinary column	Yes, by default	Any fact may simply not have been recorded yet
A foreign key	Yes	It means 'not linked to anything yet' — a subject with no teacher assigned

You can also forbid NULL in a column that ought to always have a value — a student's name, say — using the not null constraint. That is in the next chapter, where tables get built.

6Recap

NULL means

Missing, unknown or not applicable.

Not 0, not ''

Those are values somebody entered. NULL is the absence of one.

In arithmetic

Anything + NULL is NULL.

In a comparison

Even NULL = NULL is NULL, so = never finds it. Use IS NULL.

Quick Check

What does NULL in a cell mean?

Quick Check

What does select 5 + null; return?

Quick Check

What is the result of null = null in MySQL?

Quick Check

A query asks for rows where percent = NULL. What comes back?

← Previous

🔗 Foreign Key

The mysql> Prompt ⌨️