Lesson 14: Using the CASE statement.

By Alan Hylands — 3 minute read

The CASE for the defence.

The CASE statement in SQL allows you to create a new variable and set the values based on a series of conditions being met. Sounds a bit like a hostage situation but it's actually a very powerful addition to our toolbelt.

We've seen before how reducing data redundancy is one of our ideal database design states. Don't have something in there if it's not absolutely necessary.

By using the CASE statement, we can change this business logic on the fly at any time. This causes a lot less problems than having to change the underlying data in our database.

An example.

In our business we have three tiers of organizational hierarchy - Level 1 (lowest), Level 2 (mid), Level 3 (highest).

In the past we had Data Analysts, Middle Management and Senior Executives. These days our Data Analysts like to self-identify as "Data Scientist" and our Executives prefer to be known as a "Industry Visionary".

Our old CASE statement would be:

SELECT EmployeeName,
WHEN EmpLevel = 1 THEN 'Data Analyst'
WHEN EmpLevel = 2 THEN 'Middle Manager'
WHEN EmpLevel = 3 THEN 'Senior Executive'
ELSE 'Unemployed'

FROM Employees;

After changing up our job titles it would be:

SELECT EmployeeName,
WHEN EmpLevel = 1 THEN 'Data Scientist'
WHEN EmpLevel = 2 THEN 'Middle Manager'
WHEN EmpLevel = 3 THEN 'Industry Visionary'
ELSE 'Unemployed'

FROM Employees;

The underlying data in our Employees table did not have to change, it stayed exactly the same. It's just the description in our CASE statement that was amended.

(NB. If we hadn't included an ELSE in the CASE statement, it would have defaulted the values to NULL if none of the conditions were met.)

Quiz Time.

The Question.

We love all of our superheroes the same regardless of where they come from. But if we wanted to get a list of their names and their species group in two groups: 1) HUMAN and 2) ALIEN, how would you use the CASE statement to do it?

The Data.

The Editor.


Show Answer

The Answer.

Correct SQL:

                    SELECT name, CASE WHEN species = 'Human' THEN 'HUMAN' ELSE 'ALIEN' END FROM characters;

Correct Output:

Try the next lesson