SQL Intermediate 3: CASE Statement

SQL

Vanessa Gottardi

5/8/20234 min read

Welcome to the third post of our SQL Intermediate series. The SQL CASE statement is a powerful and versatile feature that allows us to perform conditional logic within queries. Whether you're a seasoned SQL developer or just starting with databases, understanding the basics of the CASE statement can greatly enhance your ability to write efficient and flexible queries. By following practical examples and exercises, you can quickly master this valuable skill and unleash the full potential of your SQL queries. Happy querying!

Hello everyone and thank you for joining us on our third SQL Intermediate series post.

At the end of this post, you can find links for SQL series 😊

One essential feature of SQL is the CASE statement, which allows us to perform conditional logic within a query. In this blog post, we will explore the fundamentals of the SQL CASE statement and provide practical examples to demonstrate its practice using our tables created in SQL Basics 1: Create Table and Add Data in SSMS to follow along with the exercises.

What is a CASE Statement in SQL?

The CASE statement is a conditional expression in SQL that allows us to control the flow of data during retrieval or manipulation. It provides a way to evaluate conditions and return different values based on those conditions.

The CASE statement can be used in SELECT, WHERE, ORDER BY, and other clauses, making it a versatile tool for data transformation and analysis.

Basic Syntax of the CASE Statement:

CASE: The keyword that marks the beginning of the CASE statement.

WHEN: A keyword used to define each condition to be evaluated.

conditionX: A boolean expression that determines whether the corresponding result will be returned.

THEN: Specifies the value to be returned if the corresponding condition is true.

resultX: The value returned when the corresponding condition is true.

ELSE: An optional keyword that specifies the default value to be returned if none of the conditions evaluate to true.

default_result: The value returned when none of the conditions evaluate to true if the ELSE keyword is present.

END: Specifies that the CASE statement is complete.

In simple words, CASE Statements in SQL allow us to specify a condition, then specify what you want returned when that condition is met.

Practical Examples:

Let’s use ‘EmployeeDemographics’ table for our first exercise and clarify what I want to say.

Example 1: Categorizing Empoyees

From our table, we want first name, last name, and age. We want to say that employees over 55 are ‘Senior’, otherwise they are ‘Junior’. We also want to say that age is not null since we are classifying the age, as well as ordering from lowest to highest (ascending by default).

Using our knowledge from previous posts 😉

“SELECT FirstName, LastName, Age,
CASE WHEN Age > 55 THEN ‘Senior’
ELSE ‘Junior’ END
FROM EmployeeDemographics
WHERE Age is NOT NULL ORDER BY Age” -> Execute

Now, let’s add another condition.

We want to say that employees between ages 45 and 55 are ‘Managers’.

“SELECT FirstName, LastName, Age,
CASE WHEN Age > 55 THEN ‘Senior’
WHEN Age BETWEEN 45 AND 55 THEN ‘Managers’
ELSE ‘Junior’ END
FROM EmployeeDemographics
WHERE Age is NOT NULL ORDER BY Age” -> Execute

NOTE: The very first condition that is met, is going to be returned. Which means that, if multiple conditions meet the criteria, only the first one is going to be returned.

Now, we say that age 55 equals ‘Superior’.

“SELECT FirstName, LastName, Age,
CASE WHEN Age > 55 THEN ‘Senior’
WHEN Age BETWEEN 45 AND 55 THEN ‘Managers’
WHEN Age = 55 THEN 'Superior'
ELSE ‘Junior’ END
FROM EmployeeDemographics
WHERE Age is NOT NULL ORDER BY Age” -> Execute

Even though we specify age = 55 is ‘Superior’, the outcome came ‘Managers’ because the condition age between 45 and 55 THEN ‘Managers’ was already met.

To fix it, let’s just change the order of the query.

“SELECT FirstName, LastName, Age,
CASE WHEN Age > 55 THEN ‘Senior’
WHEN Age = 55 THEN 'Superior'
WHEN Age BETWEEN 45 AND 55 THEN ‘Managers’
ELSE ‘Junior’ END
FROM EmployeeDemographics
WHERE Age is NOT NULL ORDER BY Age” -> Execute

Boom! Problem fixed!

The SQL CASE statement is a powerful tool for performing conditional operations within queries, enabling us to manipulate and categorize data effectively.

By using the CASE statement, we can make our SQL queries more concise, flexible, and easy to understand. Remember, the possibilities are vast, and with practice, we can master the art of utilizing the CASE statement to solve complex data problems efficiently.

Happy querying!

If you like this content and want to learn more, you might also read:

SQL Basics 1: How to Create Table and Add Data in SSMS

SQL Basics 2: SELECT + FROM Statements

SQL Basics 3: WHERE Statement

SQL Basics 4: GROUP BY and ORDER BY Statements

SQL Intermediate 1: INNER and OUTER Joins

SQL Intermediate 2: UNION Operator:

SQL Intermediate 3: CASE Statement

SQL Intermediate 4: HAVING Clause

SQL Intermediate 5: UPDATE and DELETE Data

SQL Intermediate 6: AS - Aliasing