What is the SQL CASE Statement? Provide an example and explanation.
The CASE Statement (more commonly known as the CASE Expression) allows you execute parts of a SQL statement conditionally. So, for example, you can use the CASE statement to format the results of the query conditionally – based on the values in another column.
Two general forms of the CASE expression
Note that there are actually two general forms of the CASE expression. We’ll go over one of them here and provide a link to the other one below.
SQL CASE Syntax
Here is the syntax for the simpler form of the CASE expression:
CASE input_expression /* usually a column_name */ WHEN conditional_expression1 THEN result_expression1 [WHEN conditional_expression2 THEN result_expression2] [ELSE result_expression] END
How the SQL CASE Expression works
In the SQL CASE statement shown above, each WHEN statement will be evaluated to see if the input_expression is equal to the conditional_expression. And, if that comparison returns TRUE, the corresponding result_expression will be returned and none of the other WHEN conditions will be evaluated. Basically the first matching WHEN condition will “win”. We go through an actual simple example below of how the SQL CASE statement can be used so that you can see it in action.
What if no matching condition in the SQL CASE statement?
If there is no matching condition in any of the WHEN conditions in the SQL CASE statement, and if there is also no ELSE condition, then a NULL value will be returned.
SQL CASE Example
Now, let’s go through an example of how the SQL CASE expression can be used so that you can understand it further.
Suppose we have a table called PROGRAMMING_LANGUAGES that stores different programming languages. And, for each programming language in the table, there is an associated rating from 1-3 which tells you how difficult the language is to learn and use, where 3 is the most difficult. This rating is stored in the RATING_CODE column.
Now, let’s say that we want to retrieve all of the programming languages from the table, and for each language we want to translate the code to some text so that someone unfamiliar with the rating system can understand it in plain English. This is what the CASE statement would look like:
SELECT LANGUAGE_NAME, RATING_CODE AS RATING, CASE RATING_CODE WHEN '3' THEN 'DIFFICULT' WHEN '2' THEN 'FAIRLY DIFFICULT' WHEN '1' THEN 'EASY' ELSE 'UNKNOWN' END AS RATING_DESCRIPTION FROM PROGRAMMING_LANGUAGES;
Example of SQL Case Statement explained
Let’s explain how the CASE statement works above. Note that the RATING_CODE column is used as the input_expression for the CASE statement. This just means that for each row, the value in the RATING_CODE column will be checked to see if it is a 1, 2, or 3, and depending on the value will output the corresponding text. Note that the text “Difficult”, “Fairly Difficult”, or “Easy” will be returned under the column titled “RATING DESCRIPTION”, because of the “END AS RATING_DESCRIPTION” line. Also, notice that “UNKNOWN” will be returned if a row in the table does not have a 1, 2, or 3 value in the RATING_CODE column.
Here’s a sample of what the data returned would look like if the SQL statement above is run:
LANGUAGE_NAME RATING RATING_DESCRIPTION Java 2 Fairly Difficult Ruby on Rails 3 Difficult Basic 1 Easy