SQL ‘Case’ Statement

In SQL a ‘case’ statement can be used to assess multiple conditions and return a value when the first one of these conditions is met. It can be used in a number of different places within an SQL statement, including the ‘select’, ‘where’ and ‘order by’ clauses and can take one of two formats.

CASE expression
    WHEN option1 THEN result1
    WHEN option2 THEN result2
    WHEN option3 THEN result3
    ELSE result
END
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE result
END

The first format takes an expression, for example, a column name from a table and outputs a result depending on which option, or value of that column, is met. The result in ‘else’ is used if none of the options are valid.

The ‘person‘ table, from previous examples, can be used to demonstrate how this can be used.

person
id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
2 Simon Smith Mr 1960-04-01
3 Freida Bloggs Mrs 1970-10-12
4 Fiona Jones Miss 1985-05-19

A ‘case’ statement, in this format, can take the ‘title’ column as the expression, use the possible values of this column as the options and produce a new gender column value depending on the option, or ‘Unknown’ if none of the options are met.

SELECT id, firstname, lastname, title, dob,
CASE title
   WHEN 'Mr' THEN 'Male'
   WHEN 'Mrs' THEN 'Female'
   WHEN 'Miss' THEN 'Female'
   ELSE 'Unknown'
END AS gender
FROM person;

The resulting extra column has been given a heading of ‘gender’, using the ‘AS’ keyword.

person
id firstname lastname title dob gender
1 Fred Bloggs Mr 1980-05-05 Male
2 Simon Smith Mr 1960-04-01 Male
3 Freida Bloggs Mrs 1970-10-12 Female
4 Fiona Jones Miss 1985-05-19 Female

The second format doesn’t take an expression at the start, but instead uses individual conditions to produce a result. If none of these conditions are met, then the result in the ‘else’ section is used. The example below produces the same results as above.

SELECT id, firstname, lastname, title, dob,
CASE
   WHEN title = 'Mr' THEN 'Male'
   WHEN title = 'Mrs' THEN 'Female'
   WHEN title = 'Miss' THEN 'Female'
   ELSE 'Unknown'
END AS gender
FROM person;

With this example, the first format is best because the ‘title’ is always being compared to a specific value using the equals operator. The second format is more suited to a situation where, for example, the conditions incorporate differing columns from a table, or different operators are used, such as equals in one and less than in another.

Navigation