Conditional logic with the CASE expression

CASE is SQL’s equivalent to IF-ELSE statements in other programming languages. This expression allows you to test a condition and return a value.

Introduction
Syntax
CASE with one condition
CASE with multiple conditions
Limitations and Gotchas

Introduction

In this example, I’m going to load a subset of the top-selling Sega Genesis games dataset into SQL Server.

Syntax

Each ‘WHEN’ tests to see if a condition has been met. If it has, it returns the ‘RESULT’, if not it moves on to check the next condition.

If none of the specified conditions is met it returns what we specify after ‘ELSE’.

Every CASE expression needs to finish with ‘END’.

An optional extra is to add a column name to our result.

case
    when condition1 then result1
    when condition2 then result2
    when condition3 then result3
    else result end
as columnname;

case

when condition1 then result1

when condition2 then result2

when condition3 then result3

else result end

as columnname;

This makes a lot more sense with examples, so let’s get started with our Sega Genesis dataset.

CASE with one condition

In our first example, we are using CASE to test one condition. We want to create a new column that shows if a game is from the Sonic series or not.

When the game name contains the string ‘Sonic’ the expression returns ‘Y’, if not it returns ‘N’.

The last step is to create the column name ‘Sonic Series’

select 
  game,
  year,
  case 
    when game like 'sonic%' then 'Y'
    else 'N'
    end 
  as 'Sonic Series'
from 
  sega_sales
order by 
  'Sonic Series' desc

select

game,

year,

case

when game like 'sonic%' then 'Y'

else 'N'

end

as 'Sonic Series'

from

sega_sales

order by

'Sonic Series' desc

CASE with multiple conditions

In this example, we are using CASE to test multiple conditions. We want to create a new column that shows if Global sales are low, mid or high.

When the games Global sales are greater than or equal to 4.00 the expression returns ‘high’, if not it moves on to the next step.

If the games Global sales are less than or equal to 3.99 and greater than 2.00 the expression returns ‘mid’.

If the games Global sales do not meet either of these conditions the expression returns ‘low’.

The last step is to create the column name ‘Sales Range’

select
  game, 
  global, 
  case 
    when global &gt;= 4.00 then 'high' 
    when global &gt; 2.00 and global &lt;= 3.99 then 'mid' 
    else 'low' 
    end
  as 'Sales Range' 
from
  sega_sales

select

game,

global,

case

when global >= 4.00 then 'high'

when global > 2.00 and global <= 3.99 then 'mid'

else 'low'

end

as 'Sales Range'

from

sega_sales

Limitations and Gotchas

The CASE expression is powerful and makes evaluating conditions simple. However, the expression works sequentially in the order specified so when a condition is satisfied it will stop.

Having said that, the CASE expression is flexible and can be used in ORDER BY, views, aggregates with HAVING and to UPDATE data. If you need to perform IF-ELSE conditions in SQL, give it a try.

Photo by Karolina Grabowska from Pexels