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.
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
We can actually omit the AND wind_speed < 40
condition. Here is why: the machine reads a CASE
statement from top to bottom, and ...
No credit card required