Getting Started with SQL

Chapter 7. CASE Statements

We are almost ready to learn the truly defining feature of SQL, the JOIN operator. But before we do that, we should spend a short chapter covering a very handy operator called CASE. This command allows us to swap a column value for another value based on one or more conditions.

The CASE Statement

A CASE statement allows us to map one or more conditions to a corresponding value for each condition. You start a CASE statement with the word CASE and conclude it with an END. Between those keywords, you specify each condition with a WHEN [condition] THEN [value], where the [condition] and the corresponding [value] are supplied by you. After specifying the condition–value pairs, you can have a catch-all value to default to if none of the conditions were met, which is specified in the ELSE. For example, we could categorize wind_speed into wind_severity categories (Figure 7-1), where any speed greater than 40 is 'HIGH', 30 to 40 is 'MODERATE', and anything less is 'LOW':

SELECT report_code, year, month, day, wind_speed,

CASE
    WHEN wind_speed >= 40 THEN 'HIGH'
    WHEN wind_speed >= 30 AND wind_speed < 40 THEN 'MODERATE'
    ELSE 'LOW'
END as wind_severity

FROM station_data

Figure 7-1. Categorizing wind severity into HIGH, MODERATE, and LOW

We can actually omit the AND wind_speed < 40 condition. Here is why: the machine reads a CASE statement from top to bottom, and ...