CASE Statement & Nested Case in SQL (with Examples)

In SQL, various filtering and data fetching techniques are used to fetch interrelated searches or handle complex conditions. In this article, we will learn about CASE statements and how to implement Nested Cases in SQL.

What is a CASE Statement in SQL?

CASE statement creates a conditional space where the expected output data points are based on the given conditions. It is specifically useful for implementing conditional statements within queries, to get a flexible reach around the data.

Being such a valuable asset to database management systems, CASE statements also allow the transformation of data. They evaluate every condition until they have reached a point where the condition is met allowing the generation of a suitable output.

CASE statements can be used for multiple operations such as performing search filters, handling NULL values, sorting based on custom conditions, transforming data, and deriving columns.

Types of CASE Statement

Case statements are a powerful tool, it is important to understand how different types of CASE statements can be used. It is essential to understand the method and order of execution for CASE statements to avoid errors.

1) Simple CASE Statements in SQL

The Simple CASE statements are used to check and compare whether the given condition matches with the specified value to give an expected output to the user. It allows the creation of structured data. 

Syntax:

SELECT
column1,
column2
CASE expression
    WHEN value1 THEN outcome1
    WHEN value2 THEN outcome2

    [ELSE other_operation]
END AS alias_name
FROM
your_table;

Let us understand the use of Simple CASE Statements in SQL with an example.

Table: AirplaneSales

SQL Query:

SELECT
SaleID,
SaleAmount,
CASE SaleAmount
WHEN 120000000 THEN 'High Value'
WHEN 95000000 THEN 'Medium Value'
ELSE 'Low Value'
END AS SaleCategory
FROM
AirplaneSales;

Output:

In the above example, we have used CASE statements to categorize the SaleAmount into ‘High Value’, ‘Medium Value’, and ‘Low Value’.

2) Aggregation using CASE Statements in SQL

SQL CASE statements allow aggregational functions to operate with conditional logic. This is often useful when we want to perform some operations on a dataset to get a derived column while considering different cases. 

Syntax:

SELECT
aggregation_function(CASE
  WHEN condition1 THEN outcome1
  WHEN condition2 THEN outcome2

 [ELSE other_operation]
  END) AS result_alias
FROM
table_name
WHERE
conditions;

Let us understand aggregation in CASE statements through an example

Table: AirplaneSales

SQL Query:

SELECT
COUNT(CASE WHEN SaleAmount > 100000000 THEN 1 END) AS HighValueCount,
COUNT(CASE WHEN SaleAmount > 50000000 AND SaleAmount <= 100000000 THEN 1 END) AS MediumValueCount,
COUNT(CASE WHEN SaleAmount <= 50000000 THEN 1 END) AS LowValueCount
FROM
AirplaneSales;

Output:

In the above example, we have used the COUNT aggregation to count the number of high-valued, Medium-valued, and low-valued airplanes based on their SalesAmount.

3) Nested Query Statements in SQL using CASE

Nested queries are one of the advanced methods used in CASE Statements to dive deeper into data with multidimensional features. It allows the user to apply a condition over a condition allowing an in-depth analysis. It tailors the data based on complex and interrelated conditions.

Various multiple conditional statements can be applied over the basic block of conditions. These blocks of statements test themselves on the given specification before moving out towards other outer queries.

Syntax:

SELECT
column1,
column2,
CASE
 WHEN condition1 THEN
  CASE
   WHEN nested_condition1 THEN nested_result1
   WHEN nested_condition2 THEN nested_result2
   ELSE nested_result_else
   END
  WHEN condition2 THEN result2
  ELSE result_else
END AS alias_name
FROM
table_name;

Let us understand the use of Nested Queries in CASE statements through an example

Table: AirplaneSales

SQL Query:

SELECT
SaleID,
AirplaneModel,
SaleAmount,
CustomerType,
CASE
WHEN SaleAmount > 100000000 THEN
CASE
WHEN CustomerType = 'Corporate' THEN 'High Value Corporate Sale with Special Discount'
WHEN CustomerType = 'Government' THEN 'High Value Government Sale with Special Discount'
ELSE 'High Value Sale with Standard Discount'
END
WHEN SaleAmount > 50000000 THEN
CASE
WHEN CustomerType = 'Corporate' THEN 'Medium Value Corporate Sale with Standard Discount'
WHEN CustomerType = 'Individual' THEN 'Medium Value Individual Sale with Standard Discount'
ELSE 'Medium Value Sale with Standard Discount'
END
ELSE
'Standard Sale'
END AS SaleCategory
FROM
AirplaneSales;

Output:

In the above example, we have used multiple nested statements to categorize the type of customer as well as the type of discount given to the customer.

How to Apply a Nested CASE?

In SQL, the CASE statements function works just like the if-else statements, with each block of the CASE statement testing itself individually. Here's how they operate:

1. Start by identifying the innermost condition where the test is to be applied.
2. Give the values and their corresponding actions for both the true and false results of the condition.
3. End the innermost CASE statement. If the innermost condition comes out to be false, then the system generally proceeds to the next outer query.
4. The outer query then tests its condition, and the process repeats.
5. It then continues testing outward through nested CASE statements until reaching the outermost query and ending the overall set of CASE statements.

What is the alternative to nested CASE in SQL?

Alternative ways to apply the nested CASE statements in SQL may involve the use of conditional functions such as IIF(), SWITCH(), and CHOOSE(). We can also consider using stored procedures and user-defined functions as alternatives, as these can improve code efficiency.

Conclusion

Overall in this article, we looked at various types of case statements and also focused on how nested cases in SQL. With the use of various examples, we were able to identify the workflow of these CASE statements gaining insights into their application and use cases. If you want to know more about them, connect with a professional SQL Tutor online anytime.