CASE Expression: Two Important Things

06-22-25

Nearly every major flavor of SQL (SQL Server, MySQL, PostgreSQL, Oracle, etc.) includes a CASE expression. What is a CASE expression? A simple CASE expression allows you to return different values based on equality conditions. It's similar in concept to an IF-THEN-ELSE in procedural languages.

Here are two key points about simple CASE expressions to keep in mind to ensure you get the results you expect.

1. The WHEN clauses are evaluated from left to right, and the first to return true wins. With that in mind, what are the results of the statement below?

SELECT CASE
 WHEN 2 = 1 THEN
 'Red'
 WHEN 2 = 2 THEN
 'Blue'
 WHEN 3 = 3 THEN
 'Red'
 END AS FavoriteColor;

2. If you don't specify a value for ELSE, SQL implicitly returns NULL. So, if you want to control the output in a CASE expression, always specify a value for the ELSE clause. Maintaining control over behavior is generally better than not having control. Consider why we use error handling.

SELECT CASE
 WHEN 1 = 2 THEN
 'Red'
 WHEN 2 = 1 THEN
 'Blue'
 WHEN 3 = 1 THEN
 'Red'
 ELSE
 'Game Over'
 END AS FavoriteColor

This may be for a future post, but did you know that there is a searched CASE expression beyond the simple one?