All right. The construction we've mentioned at the beginning is
CASE WHEN ... THEN ... ELSE ... END (commonly called just
CASE WHEN). If you've ever used any programming language, you could think of it as an if-then-else type of logic. If you haven't, don't worry – we'll get to that in a second.
There are two similar versions of
CASE expressions: a simple
CASE WHEN and a searched
CASE WHEN. Let's start with the first one and give you an example:
WHEN 50 THEN 'normal'
WHEN 10 THEN 'reduced'
WHEN 0 THEN 'free'
ELSE 'not available'
Looks complicated? Let's discuss it.
CASE WHEN adds a new column to the result of the query, so that's why it usually goes into the
SELECT clause with the other columns, before the
CASE WHEN takes:
- a list of one or more values, each value is listed after
- a list of return values, each return value is listed after
THEN of the appropriate condition,
- an optional
ELSE part with a default value for situations where none of the condition is met.
CASE WHEN always ends with
In our example, there are a few options in the column
fee of the table
application. The candidate may pay a full application fee (50), a reduced one (10), may not have to pay at all (0) or the payment information may be unavailable for some reason (
NULL). Instead of showing these values, we change them to more descriptive ones (
So, after the keyword
CASE we name the column of interest (
fee), and then we provide a few possible value scenarios for this column (
WHEN 50 ...,
WHEN 10 ...,
WHEN 0 ...), instructing our database what to print in each of these cases (
... THEN 'normal',
... THEN 'reduced',
... THEN 'free').
If none of the values matches the one we find in a given row, then the
ELSE part is executed (
ELSE 'not available'). In this way, we can control the output of our queries.
Note that you can have as many
WHEN ... THEN statements as you need and that the
ELSE part is always optional.