Labels and missing values

CASE chooses per row

CASE WHEN year < 1970 THEN 'classic' ELSE 'modern' END is an expression that runs once per row and produces the value of the branch it lands in. It can stand in the SELECT list, in an ORDER BY, and inside an aggregate.

The branches are read from the top, and the first true condition wins — order matters, exactly as it does with a chain of ifs. Without an ELSE, an unmatched row gets NULL, which is occasionally what you mean and more often an invitation to a second thought.

NULL spreads

Any arithmetic or comparison with NULL produces NULL — the unknown flows through the calculation. That is why a missing year turns a whole expression into nothing, and why a report tends to show blanks where the data is incomplete.

COALESCE(year, 0) returns the first value that is not NULL: the year when it is there, the stand-in otherwise. IFNULL(year, 0) is the same idea with two arguments. COALESCE accepts a whole list, so it can be a chain of fallbacks.

Where this pays off

A label built with CASE is how a report says what a number means; a stand-in from COALESCE is how the table stays printable. Together they are the difference between a dump of rows and something a person can read.

In a real database, a column that is frequently NULL is sometimes a hint that the data belongs in its own table — the schema conversation this course leaves you ready to have.

A label is text, and text sorts as text

A CASE label is text, so ORDER BY era sorts the words: classic, modern, recent. That happens to match the chronology here and would not if the branches were named old, middle and new. When the order matters, sort by the column the label was derived from, or give each branch a rank and sort on that.

A label is for reading; the rank is for ordering. Both can sit in the same SELECT, one shown and one not, and the query stays honest about which of the two it is sorting by.

CASE is an expression

CASE produces a value, so it can stand anywhere a value can: in the SELECT list, in WHERE, in ORDER BY, and inside an aggregate such as SUM(CASE WHEN ... THEN 1 ELSE 0 END). That last shape is how you count a subset without filtering the rows away.

There are two forms. The searched form writes each condition in full and is the one to reach for. The simple form compares one expression against a list — CASE status WHEN 'a' THEN ... WHEN 'b' THEN ... END — and is shorter when every branch tests the same value.

The branches should agree on a type. Mixing a number and a word is allowed in SQLite, which will store either in the same column, but the result is hard to sort and awkward to sum. A label is text in every branch.

NULL has a type of its own

NULL is not equal to anything, including itself. Two rows with a NULL in the same column are not duplicates for UNIQUE, and GROUP BY puts all of them into one group because it treats them as the same unknown.

COALESCE walks its arguments and returns the first non-NULL. IFNULL(a, b) is the two-argument shorthand, and NULLIF(a, b) goes the other way: it returns NULL when the two are equal, which is how a sentinel such as an empty string becomes a real missing value.

The functions differ in what they count. COUNT(column) skips NULLs; COUNT(COALESCE(column, 0)) counts every row, because the value handed to COUNT is never NULL. That is a feature when you mean rows and a bug when you meant values.

A label, and a year with a stand-in

SELECT title,
       CASE
         WHEN year < 1970 THEN 'classic'
         WHEN year < 1990 THEN 'modern'
         ELSE 'recent'
       END AS era,
       COALESCE(year, 0) AS year_shown
FROM books
ORDER BY year_shown;

Try these next

• Count the books per era with SUM(CASE WHEN ... THEN 1 ELSE 0 END) and no GROUP BY.
• Use NULLIF to turn an empty series name into NULL, then count the missing ones.
• Sort a report with NULL labels last by adding NULLS LAST or a computed flag.