To elaborate more, consider below example:

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 Else, I will prefer to visit some nearby tourist spot.

Let’s consider categorizing Condition and Action separately from the above example below: In the above example, we can see that the outcome of the different conditions is governing separate action. E.g., Visitor will perform the act of visiting New York only in the condition if the flight ticket is between $100 to $200. In this tutorial, you will learn:

What is CASE Statement in SQL Server? Simple CASE
Searched CASE
Difference between Simple and searched case
Nested CASE: CASE in IF ELSE
Nested CASE: CASE inside CASE
CASE with UPDATE
CASE with Order by

Let’s learn how to use Case in SQL and it’s 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

The parameter Case_Expression denotes the expression which we will eventually be compared to Value_1, Value_2, … The parameters Statement_1, Statement_2… denote the Statements which 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 ACTION is the execution of Statement_N if the above result is TRUE. ALIAS_NAME is optional and is the alias name given to SQL Server CASE statement result. Mostly used when we use Case in SQL server select clause.

Rules for Simple Case:

Simple Case only allows equality check of Case_Expression with Value_1 to Value_N. The Case_Expression is compared with 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 with Value_1, then Case_Expression is compared with Value_2 for equivalency. This process of comparing Case_Expression with Value will continue until Case_Expression finds matching equivalent value from the set of Value_1, Value_2,… If nothing matched, then control goes to ELSE statement, and Statement_Else will get executed. ELSE is optional. If ELSE is not present and Case_Expression matches with none of the values, then Null will be displayed.

Below Diagram illustrate the execution flow of Simple Case.

Examples:

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

We will use ‘Guru99’ table in further examples Query 1: SIMPLE CASE with the NO ELSE option Result: Below diagram explains the execution flow of a SIMPLE CASE with NO ELSE.

Query 2: SIMPLE CASE with the ELSE option. Result: Below diagram explains the execution flow of a SIMPLE CASE with ELSE.

Searched CASE

The syntax for Searched Case Here,

The parameter Boolean_Expression_1, …denotes the expression which will be evaluated for TRUE or FALSE. The parameters Statement_1, Statement_2… denote the Statements which will execute if its corresponding Boolean_Expression_1, Boolean_Expression_2 result is TRUE. In a nutshell, Condition is Boolean_Expression_1,… and ACTION is the execution of Statement_N if above boolean_Expression_1 is TRUE. ALIAS_NAME is optional and is the alias name given to CASE statement result. Mostly used when we use CASE in the select clause.

Rules for Searched Case:

Unlike the simple case, Searched Case is not restricted to only equality check but allows 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 TRUE condition. This process of assessing Boolean_expression will continue until one of the Boolean_expression returns TRUE. If nothing matched, then control goes to ELSE statement, and Statement_Else will get executed.

Like Simple Case ELSE is optional in Search case as well. If ELSE is not present and none of the Boolean_expression return TRUE, then Null will be displayed.

Below Diagram illustrate the execution flow of the Searched Case.

Examples:

Query 1: SEARCHED CASE with the NO ELSE option Result: Below diagram explains the execution flow of the SEARCHED CASE with NO ELSE.

Query 2: SEARCHED CASE with the ELSE option. Result: Below diagram explains the execution flow of the SEARCHED CASE with ELSE.

Difference between execution approach: SIMPLE and SEARCH CASE.

Let’s have a look at SIMPLE CASE example below: Here, ‘Tutorial_name’ is a part of CASE expression in SQL. Then ‘Tutorial_name’ value is compared with each WHEN values, i.e. ‘SQL’… until ‘Tutorial_name’ matches with WHEN values. On Contrary, SEARCH CASE example has no CASE Expression: Here, each WHEN statement has its Conditional Boolean expression. Each Boolean expression i.e. Tutorial_name = ‘SQL’,… is evaluated for TRUE/FALSE until first Boolean expression which evaluates to TRUE.

Difference between Simple and searched case

Nested CASE: CASE in IF ELSE

We can use CASE inside IF ELSE. Below is the example MS-SQL code In the above example CASE is NESTED inside IF…ELSE statement: First, IF Statement will execute and if Case Condition in SQL server is False then ELSE statement will execute. Else contain Nested CASE Statement in SQL inside it. Depending upon Flight ticket value, one amongst the following result will be displayed:

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

Nested CASE: CASE inside CASE

We can use CASE inside CASE in SQL. Below is the example MS-SQL code In the above example CASE is NESTED inside another CASE statement: The system starts with executing the outer CASE. If Flight_Ticket < $400 then inner CASE will execute. Depending upon Flight ticket value, one amongst the following result will be displayed:

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

CASE with UPDATE

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

We will use ‘Guru99’ table in further examples We can use CASE with UPDATE. Below is the example MS-SQL code: In the above example CASE is used in the UPDATE statement. Depending upon Tutorial_Name Value, Tutorial_Name column will get the update with 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’

Let’s Query Guru99 table to check the updated value:

CASE with Order by

We can use CASE with Order By. Below is the example MS-SQL code: Here CASE is used with Order By. @Order is set to 1 and as first WHEN Boolean expression evaluates to TRUE, Tutorial_ID is selected for Order by Condition

Interesting Facts!

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

Summary:

In MS SQL, there are two types of CASE: Simple CASE and Searched CASE ELSE is optional in the CASE statement.