Case Statement using SQL (examples included)

Here are 3 different ways to apply a case statement using SQL:

(1) For a single condition:

CASE WHEN condition_1 THEN result_1 ELSE result_2 END AS new_field_name

(2) For multiple conditions using AND:

CASE WHEN condition_1 AND condition_2 THEN result_1 ELSE result_2 END AS new_field_name

(3) For multiple conditions and results:

CASE WHEN condition_1 THEN result_1
     WHEN condition_2 THEN result_2
     WHEN condition_3 THEN result_3
     ...
     ELSE result
     END AS new_field_name

Let’s review few examples with the steps to apply case statements using SQL.

Steps to Apply Case Statements using SQL

Step 1: Create a Table

If you haven’t already done so, create a table in your database.

For example, let’s create a table called ‘people‘ that includes the following data and fields:

Step 2: Define the Rules

Next, define the rules for the case statement.

For example, let’s define the following rules:

• When the person’s age is equal or above 60, then the person is eligible for a ‘senior discount’
• Else, there should be ‘no discount’

Step 3: Apply the Case Statement using SQL

Finally, you can use the following template for a single condition:

CASE WHEN condition_1 THEN result_1 ELSE result_2 END AS new_field_name

For our example:

• condition_1: age >= 60
• result_1: ‘senior discount’
• result_2: ‘no discount’
• new_field_name: discount

So the complete syntax would look like this:

SELECT
name,
age, 
CASE WHEN age >= 60 THEN 'senior discount' ELSE 'no discount' END AS discount
FROM people

Run the query, and you’ll get the following result:

Additional Examples of Case Statements

Example 1: Multiple Conditions using AND

Let’s now review an example with multiple conditions, where the rules are:

• When the person’s age is equal or above 60, and the person is a member, then the person is eligible for a ‘membership gift’
• Else, there should be ‘no membership gift’

You can use this template for multiple conditions using AND:

CASE WHEN condition_1 AND condition_2 THEN result_1 ELSE result_2 END AS new_field_name

For our example:

• condition_1 AND condition_2: age >= 60 AND member = ‘Yes’
• result_1: ‘membership gift’
• result_2: ‘no membership gift’
• new_field_name: gift

So the complete syntax, for our example, would look like this:

SELECT
name,
age, 
member,
CASE WHEN age >= 60 AND member = 'Yes' THEN 'membership gift' ELSE 'no membership gift' END AS gift
FROM people

Run the query, and you’ll get the following result:

Example 2: Multiple Conditions and Results

For the final section of this guide, let’s review an example with multiple condition and results, where the rules are:

• When the person’s age is equal or above 60, then the person is eligible for a ‘senior discount’
• When the person’s age is equal or above 18, and less than 60, then there should be ‘no discount’
• Else the person is eligible for a ‘junior discount’

You can then use the following template for multiple conditions and results:

CASE WHEN condition_1 THEN result_1
     WHEN condition_2 THEN result_2
     WHEN condition_3 THEN result_3
     ...
     ELSE result
     END AS new_field_name

And here is the complete syntax for our example:

SELECT
name,
age,
CASE WHEN age >= 60 THEN 'senior discount'
     WHEN age >= 18 AND age < 60 THEN 'no discount'
     ELSE 'junior discount'
     END AS discount
FROM people

Run the query, and you’ll get:

You may also want to check the following page for additional SQL tutorials.