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.