CASE Expression Tutorial

Use the SQL CASE expression when you need to run through a list of conditions and return a value based on the first condition met.  

There are two forms of the CASE express simple and searched.    In this tutorial, we’ll focus on search, as it is more versatile if you want to learn more about the simple form, then read my blog article SQL CASE Statement – Simple and Searched Forms. 

Here is the searched form CASE expression’s structure:  

CASE 
    WHEN booleanExpression THEN result
    … 
    ELSE otherResult 
 END

In the Person table the Gender is represented as M or F.

We’ll use a CASE statement to make Gender easier to read. 

SELECT NationalIDNumber 
 , BirthDate 
 , CASE WHEN Gender = 'M' THEN 'Male' 
        WHEN Gender = 'F' THEN 'Female' 
        ELSE 'Unknown Value' 
   END as Gender
FROM HumanResources.Employee; 

Here is how this CASE expression works:

Each WHEN test for gender. The first TRUE Boolean expression returns. If no matches are found, such as when the column value is NULL, then CASE returns ‘Unknown Value.’

Here are some things to keep in mind when using CASE:

• The Searched CASE can test for other conditions, such as greater than, or less than, in addition to equality.
• The value returned corresponds to the first WHEN Boolean expression to match TRUE.
• If no Boolean expression matches TRUE, then CASE seeks to return the value specified with the ELSE. If there is no ELSE clause, then NULL is returned.

Additional CASE Expression Resources 

To learn more about the CASE expression, check out these useful resources: 

• SQL CASE Statement – Simple and Searched Forms. 
• Introduction to SQL Servers Built-In Logical Functions
•  SQL Cook Book