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.
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:
