CASE EXPRESSION

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 searched, 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.

CASE Expression Results

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 FROM HumanResources.Employee; 
SELECT NationalIDNumber 
 , BirthDate 
 , CASE WHEN Gender = 'M' THEN 'Male' 
        WHEN Gender = 'F' THEN 'Female' 
        ELSE 'Unknown Value' 
   END 
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 match 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: 

>