Conditional statements are very prevalent when working with databases. For example, we can use the CASE keyword in SQL to provide a set of evaluated conditions. We can then execute the code block if a given statement is true.
A case statement is similar to a nested if…else block which allows you to test a list of conditions and return only one value from the list of possible outcomes.
You will often find using the CASE statements in stored procedures or SQL functions to run a set of conditions.
This tutorial teaches us how to work with CASE statements in standard SQL.
SQL Case Statement
We can express the syntax of a CASE statement in SQL as shown in the following:
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
If you want a full demonstration on how to work with the CASE statement in SQL, check the following tutorial on the provided link:
https://linuxhint.com/sql-case-statement/
This tutorial mainly focuses on building a nested case statement in conjunction with the WHEN clauses.
Nested CASE WHEN Statements
In SQL, we can use a set of nested CASE WHEN statements in SQL to evaluate the multiple conditions and return a different result based on the defined conditions.
We can define the syntax of a nested CASE WHEN statement as shown in the following:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
FROM table_name;
Let’s take a real-world database such as the Sakila database to learn how to accomplish this:
title,
CASE
WHEN rental_rate < 1 THEN
CASE
WHEN rental_duration <= 3 THEN 'Low rate (3 days or less)'
ELSE 'Low rate (more than 3 days)'
END
WHEN rental_rate < 3 THEN 'Moderate rate'
ELSE 'High rate'
END AS rental_category
FROM film;
In the previous example, we start by selecting the film titles and grouping each film by its rental rate. Finally, we use the nested CASE WHEN statements to create different groups based on the rental rate.
We then check the rental durations and return the corresponding message. An example output is as follows:
Conclusion
We explored working with the nested case statements to evaluate a set of conditions using the WHEN clause.
