SQL CASE Statement or Expression

SQL CASE statement or SQL CASE expression is a way to perform a particular task based on some conditions. This article describes how to use CASE expression in SQL. Summary of the article:

• What is SQL CASE Statement/Expression?
• Types of SQL CASE Statement
• Simple CASE Expressions
• Searched CASE Expression

What is SQL CASE Statement/Expression?
It is a mechanism to select one value from different values based on conditions. It has WHEN, THEN, and ELSE clauses with an END terminator

Types of SQL CASE Statement
The SQL CASE expression has two formats:

1. Simple CASE Expression –compares an expression to a set of simple expressions to determine the result.
2. Searched CASE Expression -evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE statement. We can use CASE in any statement or clauses. For example, we can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select list, IN, WHERE, ORDER BY, and HAVING.

Simple CASE Expressions
It checks one expression against multiple values.

Syntax of Simple CASE Expressions
General Syntax of Simple CASE Expressions:

CASE input_expression
 WHEN when_expression THEN result_expression [ ...n ]
 [ELSE else_result_expression ]
END

Example of Simple CASE Expressions
Example of Simple CASE Expressions is given bellow:

DECLARE @Temp TABLE (
 StudentID int,
 StudentName nvarchar(100),
 CountryID char(2)
)

INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (1,'AAAA','UK')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (2,'BBBB','US')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (3,'CCCC','CH')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (4,'DDDD','UK')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (6,'EEEE','GE')

SELECT
CASE CountryID
 WHEN 'UK' THEN 'United Kingdom'
 WHEN 'US' THEN 'United States'
 ELSE 'Not Defined'
END
FROM @Temp

Here at first a temporary table variable is declared and some sample row data is inserted. Then a simple CASE statement is executed. After the execution of above code we will get the following output:

Searched CASE Expressions
A searched CASE expression allows comparison operators and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and cannot contain Boolean expressions.

Syntax of Searched CASE Expressions
A general syntax of searched CASE expressions is given bellow:

CASE
 WHEN Boolean_expression THEN result_expression [ ...n ]
 [ELSE else_result_expression ]
END

Example of Searched CASE Expressions
A simple but complete example of searched CASE expressions is given bellow:

DECLARE @Temp TABLE (
 StudentID int,
 StudentName nvarchar(100),
 CountryID char(2)
)

INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (1,'AAAA','UK')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (2,'BBBB','US')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (3,'CCCC','CH')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (4,'DDDD','UK')
INSERT into @Temp(StudentID, StudentName,CountryID) VALUES (6,'EEEE','GE')

SELECT
CASE
 WHEN CountryID ='UK' THEN 'United Kingdom'
 WHEN CountryID='US' THEN 'United States'
 ELSE 'Not Defined'
END
FROM @Temp

Here at first we have declared a temporary table variable.  Then some sample row data is inserted. Then a searched CASE statement is executed. After execution we will get the following output:

Both Simple CASE statement, Searched CASE statement will return the same output. We can use any one of them according to our choice.