SQL: Using CASE Statements

In past posts, I have written about databases and data tables, entering data, data types, and using different types of functions in query statements. In this post, I will go over using CASE statements in a SQL query.

So far, we have used string and aggregate functions in queries, but what if you need a new column based on information from another column, where there are a select group of results depending on that information? If one already knows other programming languages, you have most likely seen something similar in an if else or elif statement. The CASE statement acts in a similar way.

An example of when to use CASE statements could be in the film industry. The company wants to know which movies do the best based on their production budget. Each movie could have widely different production budgets, so for analysis the budgets are separated into three different groups: low, mid, and high budgets. By using the CASE statement, we can determine which budget level each movie belongs to.

To do so, we will look at the basic formula for the CASE statement, which is the following:

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    WHEN condition_n THEN result_n
    ELSE result
END

As in a multiple if statement, the computer will first check the first condition to see if it is true. If the condition is false, then the computer will check the following condition to see if it true. This process continues until one of the conditions is true or no conditions are true and the computer uses the else statement. It is important to realize if there is no result from an else statement and none of the conditions are true, the computer will automatically enter a NULL value.

One piece of advice is to use an ALIAS with CASE statements. When the computer produces the resulting table of a query and a CASE statement is used, the whole CASE statement is used as the column’s name. This can cause the resulting table to look bulky or hard to understand. By using an ALIAS, you can choose a much more reasonable column name.

Another piece to note is that the CASE statement does not use commas between the different conditions. If a comma is used after one of the when and then statements, the computer will expect that the following code will be to select a new column of information for the query. It is also better to have each WHEN and THEN statement be on a new line for better readability.

Going back to the movie budget example, we can separate the movie budgets into the three tiers by using the CASE statement.

SELECT 
    title,
    CASE
        WHEN production_budget >= 100000000 THEN 'HIGH'
        WHEN production_budget >= 25000000 THEN 'MID'
        ELSE 'LOW'
    END AS 'budget_category'
FROM movie_data;

From the above query, we are selecting the titles of the movies in the data table and then we use the CASE statement to separate the production budget data into one of three tiers with the AS statement creating an ALIAS for the column.

The IF Function

When talking about CASE statements, it is also useful to consider the IF function. The CASE statement is best used when there are more than 2 possible results like in the example above. The IF function can be used when there are only one or two results.

The formula of the IF functions if the following:

IF(condition, result_if_true, result_if_false)

As seen above, the function will check if the given condition is true, and if it is, it will return the first result. If the given condition is false, it will return the second result. The third variable in the function or the result if false, could be considered the else statement.

For example, instead of three budget tiers, there only needs to be high and low budget. The new query could appear as the following:

SELECT
  title,
  IF(product_budget >= 50000000, 'High', 'LOW') AS 'budget_category'
FROM movie_data;

I hope this post helps your understanding of using CASE statements and the IF function in queries. Thank you for reading.

For more documentation on CASE statements:

MySQL :: MySQL 8.0 Reference Manual :: 13.6.5.1 CASE Statement

For more documentation on the IF function:

MySQL :: MySQL 8.0 Reference Manual :: 12.5 Flow Control Functions