Working with Single-Table Data

Working with NULL

---

After learning how to filter rows, we need one extra idea: sometimes a column does not contain an ordinary value at all.

For example:

• Which submissions do not yet have a score?
• Which submissions already have a score?

In SQL, this situation is represented with NULL. It is a useful tool for representing missing or unknown values, but it also requires careful handling.

Tony Hoare famously called the null reference his “billion-dollar mistake”. SQL NULL raises some of the same kinds of confusion: it does not mean zero, and it does not mean an empty string. It means that a value is missing, unknown, or not applicable.

A Nullable Column

A nullable column is a column that is allowed to contain NULL.

Suppose the submissions table contains a score that is only filled in after grading.

id	exercise_id	user_id	score	status
1	7	42	NULL	submitted
2	7	43	9	graded

The first row does not yet have a score value.

Loading Exercise...

Testing for NULL

Suppose the question is: “Which submissions do not yet have a score?”

A common beginner mistake is this:

SELECT *
FROM submissions
WHERE score = NULL;

This does not work the way many people expect.

This is one of the first places where SQL can feel surprising. It is easy to expect NULL to behave like an ordinary value, but it does not.

To test for NULL, SQL uses IS NULL and IS NOT NULL.

SELECT *
FROM submissions
WHERE score IS NULL;

Read this in words as: keep the rows where no score is stored.

Similarly, WHERE score IS NOT NULL keeps only the rows where a score is present.

Loading Exercise...

NULL Is Tricky

NULL does not behave like an ordinary number or string. In SQL, an ordinary comparison involving NULL does not evaluate simply to true or false. It evaluates to unknown.

For example, a condition such as score = NULL does not become true, even when the score is missing. It becomes unknown, which is why IS NULL is needed instead. This is sometimes called three-valued logic: true, false, and unknown.

For now, the main point is simple: NULL means a missing or unknown value, and it should be checked with IS NULL or IS NOT NULL.

Poor designs can make NULL more confusing. For example, if a text column is nullable, it is possible to have both NULL and '' (the empty string) as values. This can be confusing because they both represent “no meaningful value”, but they are not the same thing in SQL. In that case, it is important to understand the difference and to check for both if you want to find all the “empty” values.

Sometimes a missing value is fine in the database, but awkward in query results or on a web page. In those cases, later chapters will use tools such as COALESCE to show a fallback value.

Loading Exercise...

Check Your Understanding

1. Why is = NULL not the right way to test for missing values?
2. What does IS NULL mean?
3. Why can NULL require more careful thinking than an ordinary string or number value?

SQL Practice

Complete the SQL sequence below to practice recognizing missing values and checking them with IS NULL and IS NOT NULL.

Loading Exercise...