case statements in sql

Case Statements in SQL Complete Guide [2021]

Let’s explore what are case statements in SQL and how we can apply the knowledge to solve real-world problems. If you have done any programming, you should be familiar with conditional logic. In a typical program, you can use conditional logic to control the flow of the program. 

e.g  IF ELSE THEN keywords or even the SWITCH CASE. 

In SQL, you can use it to control the output based on the values in the column or the values returned from nested expressions. This can be used in SELECT, INSERT, UPDATE, or even to DELETE data. This can be used to do various operations, such as categorized data into different sets, Transform data into different tables, do various updates based on the conditions.

Syntax of the Case Statement in SQL

CASE
    WHEN some_condition THEN return_some_value
    ELSE return_some_other_value
END

Simple real-world example on case statements in SQL

Let’s assume that we have a players score table and we need to filter out players who scored more than 25 goals in this season, and categorized them as TOP Players.

player_namecountryclubnumber_of_goals
MessiArgentinaFCB35
PedriSpainFCB8
NeymarBrazilPSG23
RonaldoPortugalJuventus30
CavariUruguayMU7

SELECT player_name, country, club,
    CASE
        WHEN number_of_goals > 25 THEN "TOP PLAYER"
        ELSE "STANDARD PLAYER"
    END goal_status
FROM players;

We’ll get the results in a categorized manner putting players into different classes. Observe the new column called goal_status dividing the players into three sets.

player_namecountryclubgoal_status
MessiArgentinaFCBTOP PLAYER
PedriSpainFCBSTANDARD PLAYER
NeymarBrazilPSGSTANDARD PLAYER
RonaldoPortugalJuventusTOP PLAYER
CavariUruguayMUSTANDARD PLAYER

Advanced real-world examples of Case statements in SQL

Next, we’ll look into more complex real-world problems, and how we can leverage our case statements in SQL knowledge to solve them

Transform data using case statements in SQL

In this use case, we are going to transform the above players’ table data to produce a summary. Say that, we have a requirement to get the total number of goals scored for a particular club by their players, and come up with a summary table. 

SELECT 
       sum(CASE WHEN club = 'FCB' THEN number_of_goals ELSE 0 END) fcb_total,
       sum(CASE WHEN club = 'MU' THEN number_of_goals ELSE 0 END) mu_total,
       sum(CASE WHEN club = 'PSG' THEN number_of_goals ELSE 0 END) psg_total,
       sum(CASE WHEN club ='Juventus' THEN number_of_goals ELSE 0 END) juventus_total
from players; 

Here we are using the SQL sum() function to get the total and in the case condition, we add to each column’s total count when there is a match for the club name. This query will produce the following transformed output.

fcb_totalmu_totalpsg_totaljuventus_total
43253030

Conditional Update using Case Statements in SQL

In this example, we perform an update a row using a case statement to check the conditions. In the above player table. Assume that we have added a new column called “status”, to categorize the player as a “TOP PLAYER” or a “PLAYER” based on the number of goals the player has scored. 

Ok, let’s take one step back and think, what would you do if you are to perform this task. You would write two update queries right?. One to set the “TOP PLAYER” where the player has scored more than 25 goals.

UPDATE players set status = "TOP PLAYER" where number_of_goals > 25 ;
UPDATE players set status = "PLAYER" where number_of_goals < 25;

Let’s write a query to fill the status field checking the number of goals using the case statement in the SQL technique. 

UPDATE players 
   SET status = 
        CASE WHEN number_of_goals > 25 THEN "TOP PLAYER"
            ELSE "PLAYER"
        END
   WHERE number_of_goals > 0;

Note: You may probably end up with an error saying something like “ERROR CODE 1175 you are using safe mode..”. This is because your MySQL session has a safe updates option set.  Use the following command to overcome this.

SET SQL_SAFE_UPDATES = 0;

Dealing with null values

When doing calculations inside switch case conditions, null values can be considered and handled programmatically to produce meaningful readable results. If null not handled then most of the time the result is also going to be NULL.

Let’s say for the above players’ table, there are players with NULL values in the number_of_goals column, and we want to group them into TOP_PLAYER, STANDARD PLAYER, NOT RATED, based on the average goals per match. 

Add an additional column to players table called, num_of_matches.

player_namecountryclubnumber_of_goalsnumber_of_matches
MessiArgentinaFCB3510
PedriSpainFCB810
NeymarBrazilPSG2315
RonaldoPortugalJuventus3012
CavariUruguayMU710
DybalaArgentinaJuventusNULL2

Below is the CASE statement we handle the NULL values when putting the data into sets.

select player_name,
    CASE
        WHEN (number_of_goals / number_of_matches) IS NULL THEN "NOT RATED"
        WHEN (number_of_goals / number_of_matches) > 2  THEN "TOP PLAYER"
        ELSE "STANDARD PLAYER"
    END
player_status from player;

If we take the player Dybala since he has not scored any goal average is screaming out as NULL. it was handled and added to the NOT RATED set. Following is the output of the above query.

player_nameplayer_status
MessiTOP PLAYER
PedriSTANDARD PLAYER
NeymarSTANDARD PLAYER
RonaldoTOP PLAYER
CavariSTANDARD PLAYER
DybalaNOT RATED

That it folks!

1 thought on “Case Statements in SQL Complete Guide [2021]”

Leave a Comment

Your email address will not be published. Required fields are marked *