CASE Statement & Nested Case in SQL Server: T-SQL Example

Overview of Case in real life!

Again, in real life, we perform different actions depending on the outcome of different conditions. To elaborate further, consider the example below:

• If flight tickets are less than $100, then I will visit Los Angeles.
• If flight tickets are between $100 to $200, then I will visit New York.
• If flight tickets are between $200 to $400, then I will visit Europe.
• Otherwise, I will prefer to visit a nearby tourist spot.

Let us consider categorizing the condition and the action separately from the above example:

In the above example, we can see that the outcome of the different conditions governs a separate action. For example, the visitor will perform the act of visiting New York only in the condition that the flight ticket is between $100 to $200. Similarly, the MS SQL CASE statement also provides the capability to execute different T-SQL statements based on the outcome of different conditions.

What is CASE Statement in SQL Server?

CASE Statement in SQL Server is the extension of the IF…ELSE statement. Unlike IF…ELSE, where a maximum of only one condition is allowed, CASE allows the user to apply multiple conditions to perform different sets of actions in MS SQL. It returns a corresponding value associated with the condition defined by the user.

Let us learn how to use CASE in SQL and its concept in the following sections. In MS SQL, there are two types of CASE:

• Simple CASE
• Searched CASE

Simple CASE

The Syntax for Simple Case

CASE <Case_Expression>
     WHEN Value_1 THEN Statement_1
     WHEN Value_2 THEN Statement_2
     .
     .
     WHEN Value_N THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Here:

• The parameter Case_Expression denotes the expression that will eventually be compared to Value_1, Value_2, and so on.
• The parameters Statement_1, Statement_2… denote the statements that will be executed if Case_Expression = Value_1, Case_Expression = Value_2, and so on.
• In a nutshell, the condition is whether Case_Expression = Value_N, and the action is the execution of Statement_N if the above result is TRUE.
• ALIAS_NAME is optional and is the alias name given to the SQL Server CASE statement result. It is mostly used when we use CASE in the SQL Server SELECT clause.

Rules for Simple Case

• Simple Case only allows an equality check of Case_Expression with Value_1 to Value_N.
• The Case_Expression is compared with a value, in order, starting from the first value, i.e., Value_1. Below is the execution approach:
• If Case_Expression is equivalent to Value_1, then further WHEN…THEN statements are skipped, and CASE execution will END immediately.
• If Case_Expression does not match Value_1, then Case_Expression is compared with Value_2 for equivalency. This process of comparing Case_Expression with a value continues until Case_Expression finds a matching equivalent value from the set of Value_1, Value_2, and so on.
• If nothing matches, then control goes to the ELSE statement, and Statement_Else is executed.
• ELSE is optional.
• If ELSE is not present and Case_Expression matches none of the values, then NULL is displayed.

The diagram below illustrates the execution flow of a Simple Case:

Examples

Assume that we have a table named ‘Guru99’ with two columns, Tutorial_ID and Tutorial_name, and four rows, as displayed below. We will use this ‘Guru99’ table in the examples that follow.

Query 1: SIMPLE CASE with the NO ELSE option

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Result: The diagram below explains the execution flow of a SIMPLE CASE with NO ELSE.

Query 2: SIMPLE CASE with the ELSE option

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Result: The diagram below explains the execution flow of a SIMPLE CASE with ELSE.

Searched CASE

The Syntax for Searched Case

CASE 
     WHEN <Boolean_Expression_1> THEN Statement_1
     WHEN <Boolean_Expression_2> THEN Statement_2
     .
     .
     WHEN <Boolean_Expression_N> THEN Statement_N
     [ELSE Statement_Else]   
END AS [ALIAS_NAME]

Here:

• The parameter Boolean_Expression_1… denotes the expression that will be evaluated for TRUE or FALSE.
• The parameters Statement_1, Statement_2… denote the statements that will execute if the corresponding Boolean_Expression_1, Boolean_Expression_2 result is TRUE.
• In a nutshell, the condition is Boolean_Expression_1… and the action is the execution of Statement_N if the above Boolean_Expression_1 is TRUE.
• ALIAS_NAME is optional and is the alias name given to the CASE statement result. It is mostly used when we use CASE in the SELECT clause.

Rules for Searched Case

• Unlike the Simple Case, the Searched Case is not restricted to only an equality check but allows a Boolean expression.
• The Boolean expression is evaluated, in order, starting from the first Boolean expression, i.e., Boolean_Expression_1. Below is the execution approach:
• If Boolean_Expression_1 is TRUE, then further WHEN…THEN statements are skipped, and CASE execution will END immediately.
• If Boolean_Expression_1 is FALSE, then Boolean_Expression_2 is evaluated for a TRUE condition. This process of assessing the Boolean expression continues until one of the Boolean expressions returns TRUE.
• If nothing matches, then control goes to the ELSE statement, and Statement_Else is executed.
• Like the Simple Case, ELSE is optional in the Searched Case as well.
• If ELSE is not present and none of the Boolean expressions returns TRUE, then NULL is displayed.

The diagram below illustrates the execution flow of the Searched Case:

Examples

Query 1: SEARCHED CASE with the NO ELSE option

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Result: The diagram below explains the execution flow of the SEARCHED CASE with NO ELSE.

Query 2: SEARCHED CASE with the ELSE option

SELECT Tutorial_ID, Tutorial_name,
CASE 
	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Result: The diagram below explains the execution flow of the SEARCHED CASE with ELSE.

Difference between Execution Approach: SIMPLE and SEARCH CASE

Let us have a look at the SIMPLE CASE example below:

SELECT Tutorial_ID, Tutorial_name,
CASE Tutorial_name
	WHEN 'SQL' THEN 'SQL is developed by IBM'
	WHEN 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
	ELSE 'This is NO SQL language.'
END AS Description
FROM Guru99

Here, ‘Tutorial_name’ is a part of the CASE expression in SQL. Then the ‘Tutorial_name’ value is compared with each WHEN value, i.e., ‘SQL’… until ‘Tutorial_name’ matches a WHEN value.

On the contrary, the SEARCH CASE example has no CASE expression:

SELECT Tutorial_ID, Tutorial_name,
CASE 
 	WHEN Tutorial_name = 'SQL' THEN 'SQL is developed by IBM'
	WHEN Tutorial_name = 'PL/SQL' THEN 'PL/SQL is developed by Oracle Corporation.'
	WHEN Tutorial_name = 'MS-SQL' THEN 'MS-SQL is developed by Microsoft Corporation.'
END AS Description
FROM Guru99

Here, each WHEN statement has its own conditional Boolean expression. Each Boolean expression, i.e., Tutorial_name = ‘SQL’… is evaluated for TRUE/FALSE until the first Boolean expression that evaluates to TRUE.

Difference between Simple and Searched Case

Nested CASE: CASE in IF ELSE

We can use CASE inside an IF…ELSE statement. We declare a variable for the flight ticket and branch on its value. Below is the example MS-SQL code:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
   PRINT 'Visit Nearby Tourist Location';
ELSE 
BEGIN
    SELECT
	CASE 
	WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
	WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
	WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
	END AS Location	
END

In the above example, CASE is nested inside the IF…ELSE statement. First, the IF statement executes, and if the CASE condition in SQL Server is False, then the ELSE statement executes. The ELSE part contains a nested CASE statement in SQL. Depending upon the flight ticket value, one of the following results is displayed:

• The system prints ‘Visit Nearby Tourist Location’ if flight tickets are > $400.
• The system prints ‘Visit Los Angeles’ if flight tickets are BETWEEN $0 AND $100.
• The system prints ‘Visit New York’ if flight tickets are BETWEEN $101 AND $200.
• The system prints ‘Visit Europe’ if flight tickets are BETWEEN $201 AND $400.

The result below shows the location returned when the nested CASE runs inside the ELSE branch:

Nested CASE: CASE inside CASE

We can use CASE inside another CASE in SQL. Below is the example MS-SQL code:

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 250;
SELECT
CASE 
WHEN @Flight_Ticket >= 400 THEN 'Visit Nearby Tourist Location.'
WHEN @Flight_Ticket < 400 THEN 
    	CASE 
		WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
		WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
		WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
		END	
END AS Location

In the above example, CASE is nested inside another CASE statement. The system executes the outer CASE first; if Flight_Ticket < $400, the inner CASE runs. The returned location follows the same ticket ranges as the previous example, so a $250 ticket falls in the $201–$400 range and returns ‘Visit Europe’.

The result below shows the location returned by the inner CASE for a $250 ticket:

CASE with UPDATE

Assume again that we have the ‘Guru99’ table with two columns and four rows, as displayed below:

We can use CASE with UPDATE. Below is the example MS-SQL code:

UPDATE Guru99
SET Tutorial_Name = 
	(
	CASE
	WHEN Tutorial_Name = 'SQL' THEN 'Structured Query language.'
	WHEN Tutorial_Name = 'PL/SQL' THEN 'Oracle PL/SQL'
	WHEN Tutorial_Name = 'MSSQL' THEN 'Microsoft SQL.'
	WHEN Tutorial_Name = 'Hadoop' THEN 'Apache Hadoop.'
	END
	)

In the above example, CASE is used in the UPDATE statement. Depending upon the Tutorial_Name value, the Tutorial_Name column is updated with the THEN statement value:

• If Tutorial_Name = ‘SQL’, then update Tutorial_Name to ‘Structured Query language’.
• If Tutorial_Name = ‘PL/SQL’, then update Tutorial_Name to ‘Oracle PL/SQL’.
• If Tutorial_Name = ‘MSSQL’, then update Tutorial_Name to ‘Microsoft SQL’.
• If Tutorial_Name = ‘Hadoop’, then update Tutorial_Name to ‘Apache Hadoop’.

Running the statement updates the matching rows, as shown below:

Let us query the ‘Guru99’ table to check the updated values:

CASE with Order by

We can use CASE with ORDER BY. Below is the example MS-SQL code:

Declare @Order Int;
Set @Order = 1
Select * from Guru99 order by 
CASE 
	WHEN @Order = 1 THEN Tutorial_ID
	WHEN @Order = 2 THEN Tutorial_Name
	END
DESC

Here, CASE is used with ORDER BY. @Order is set to 1, and as the first WHEN Boolean expression evaluates to TRUE, Tutorial_ID is selected for the ORDER BY condition. The result is shown below:

Interesting Facts!

• CASE can be nested in another CASE as well as in an IF…ELSE statement.
• In addition to SELECT, CASE can be used with other SQL clauses like UPDATE and ORDER BY.

FAQs