Programming languages offer several constructs to implement branching, including the switch and if-else statements. Ever wondered if databases have something similar? Considering how crucial and common that operation is, obviously yes. The SQL CASE statement is the tool you are looking for, as it simplifies conditional logic by allowing complex conditional branching logic directly into queries.
In this article, you will learn what CASE is in SQL, how it works, and when to use it through real-world examples.
Time to become a master of conditional logic in SQL!
What Is the CASE Statement in SQL?
The SQL CASE statement is a conditional branching expression that evaluates several expressions and returns the value associated with the first expression that evaluates to true. In other words, the CASE statement in SQL works similarly to the switch or if-else constructs of programming languages.
The CASE SQL expression is part of the ISO/ANSI SQL-92 standard. This means that most database technologies support it, including MySQL, PostgreSQL, SQL Server, and Oracle. For specific information, follow these documentation links:
Note that some DMS systems, such as MySQL, also have a variant of CASE that can be used only in SQL stored procedures.
SQL CASE: Syntax and First Examples
The SQL CASE expression can have two formats:
Let’s see them both!
Simple CASE Expression
This is what the syntax of the first type of CASE as SQL command looks like:
1
CASE expression
2
WHEN compare_expression_1 THEN result_1
3
[WHEN compare_expression_2 THEN result_2]
4
[...]
5
[WHEN compare_expression_N THEN result_N]
6
[ELSE result_E]
7
END
This returns the result_x value of the first WHEN branch where the expression=compare_expression_X comparison is true.
Thus, the simple CASE SQL expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If any of these two expressions are equivalent, the expression in the THEN clause is returned.
Consider the example below:
1
SELECT
2
CASE 3
3
WHEN 1 THEN 'one'
4
WHEN 2 THEN 'two'
5
WHEN 3 THEN 'three'
6
ELSE '—'
7
END;
Since 3 is equal to 3 in the third branch, that will produce:
1
'three'
Searched CASE Expression
The second CASE WHEN SQL expression has the syntax below:
1
CASE
2
WHEN boolean_expression_1 THEN result_1
3
[WHEN boolean_expression_2 THEN result_2]
4
[...]
5
[WHEN boolean_expression_N THEN result_N]
6
[ELSE result_E]
7
END
This returns the result_X value of the first WHEN branch where boolean_expression_X is true.
Take a look at this basic example:
1
SELECT
2
CASE
3
WHEN 4>1 THEN 'true'
4
ELSE 'false'
5
END;
It will return:
1
'true'
General Notes
The CASE statement in SQL can be used in SELECT, UPDATE, and DELETE statements as well as in IN, WHERE, ORDER BY, and HAVING clauses. In particular, you can employ it in any SQL statement or clause that allows a valid expression.
Keep in mind that the CASE as SQL command must have at least one WHEN branch. Also, both forms accept an optional ELSE branch. When no branch is true the CASE operator returns the value contained in this ELSE branch. Otherwise, if there is no ELSE branch, it returns NULL, as in the following example:
1
SELECT
2
CASE 4
3
WHEN 1 THEN 'one'
4
WHEN 2 THEN 'two'
5
WHEN 3 THEN 'three'
6
END;
This will return:
1
NULL
While the first WHEN branch is mandatory, all the others are optional. Writing a CASE expression without at least a WHEN branch will then raise an SQL syntax error.
When the branches contain data of different data types, the result will be cast to an aggregated data type. Consult the documentation of your specific DBMS for more information on the casting rules.
Use Cases of the CASE SQL Expression
The SQL CASE statement is a powerful flow control tool that enables you to handle specific conditional situations. No wonder, it has many applications in real-world scenarios.
Time to explore some of the most common CASE WHEN SQL use cases!
The following queries will be written in MySQL and executed in DbVisualizer, the database client with the highest user satisfaction in the market. Note that you can easily adapt them to any other SQL dialect and run them in your favorite client.
Data Classification
Suppose we want to assign football players a grade based on the number of goals they scored in the season. The idea is to assign a label to each football player based on how good they are at scoring. This is a common example of data classification.
You can get the desired result with a CASE SQL expression as in the following query:
1
SELECT
2
name,
3
goals,
4
CASE
5
WHEN goals_scored >= 30 THEN 'Fantastic scorer'
6
WHEN goals_scored >= 15 THEN 'Great scorer'
7
WHEN goals_scored >= 5 THEN 'Solid scorer'
8
ELSE 'Bas scorer'
9
END AS score_grade
10
FROM
11
players;
The conditions in the branches are not mutually exclusive, but that is not a problem since the CASE as SQL command will stop at the first condition that matches.
Launch it, and you will get the following results:
Mission complete! score_grade is an easy-to-understand label to classify football scorers.
Conditional Arithmetic Calculations
Suppose it is the Christmas season and you want to automatically generate updated prices for your products. Specifically, you want to apply different discount rates based on the category and price range of each product.
These are the rules you want to apply:
Thanks to the CASE expression in SQL, you can apply those rules and get the updated prices with a single query as follows:
1
SELECT
2
name,
3
category,
4
price AS original_price,
5
CASE
6
WHEN category = 'Electronics' AND price >= 1500 THEN price * (1 - 0.3)
7
WHEN category = 'Shoes' THEN price * (1 - 0.15)
8
WHEN category = 'Sports' THEN price * (1 - 0.5)
9
WHEN category = 'Clearance' AND price <= 50 THEN price * (1 - 0.5)
10
ELSE price
11
END AS christmas_discounted_price
12
FROM
13
products;
Launch it, and it will produce:
Fantastic! Automatically doing conditional arithmetic calculations by applying different formulas has never been easier.
Complex Data Segmentation
Consider a scenario where you need to calculate a performance bonus based on the employee's job role and the number of years they have been with the firm. These are the rules to apply for bonus generation:
You can generate the bonuses with the complex CASE SQL expression below:
1
SELECT
2
name,
3
job_role,
4
years_with_company,
5
salary,
6
CASE
7
WHEN job_role = 'Manager' THEN
8
CASE
9
WHEN years_with_company >= 5 THEN salary * 0.1
10
ELSE salary * 0.05
11
END
12
WHEN job_role = 'Developer' AND years_with_company >= 3 THEN salary * 0.08
13
WHEN job_role = 'Analyst' THEN
14
CASE
15
WHEN years_with_company >= 5 THEN salary * 0.04
16
ELSE salary * 0.03
17
END
18
WHEN job_role = 'Sales' THEN salary * 0.06
19
ELSE 0
20
END AS bonus
21
FROM
22
employees;
The result set will be:
Note that SQL CASE WHEN branches can contain other CASE expressions. So, you can nest a CASE
expression inside another CASE expression in SQL for maximum flexibility!
Best Practices of the SQL CASE Flow Control Statement
Here are some best practices to keep in mind when using CASE in any SQL dialect:
Conclusion
In this guide, you understood what the CASE statement in SQL is, why there are two forms of it, and how these work. You now know that the SQL CASE expression is a flow control operator to implement branching logic in your queries. Thanks to the use cases presented here, you have also learned how to use it in real-world scenarios.
CASE is a complex statement that requires a powerful database client. The best one based on user satisfaction? DbVisualizer! This full-featured database client supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free!
FAQ
How is the CASE SQL expression specified in Standard SQL?
The ISO/ANSI SQL-92 standard specified CASE as:
1
<case specification> ::=
2
<simple case>
3
| <searched case>
4
5
<simple case> ::=
6
CASE <case operand>
7
<simple when clause>...
8
[ <else clause> ]
9
END
10
11
<searched case> ::=
12
CASE
13
<searched when clause>...
14
[ <else clause> ]
15
END
16
17
<simple when clause> ::= WHEN <when operand> THEN <result>
18
19
<searched when clause> ::= WHEN <search condition> THEN <result>
20
21
<else clause> ::= ELSE <result>
22
23
<case operand> ::= <value expression>
24
25
<when operand> ::= <value expression>
26
27
<result> ::= <result expression> | NULL
28
29
<result expression> ::= <value expression>
Some general notes that complement that definition are:
1
1) Case:
2
3
a) If a <result> specifies NULL, then its value is the null
4
value.
5
6
b) If a <result> specifies a <value expression>, then its value
7
is the value of that <value expression>.
8
9
2) Case:
10
11
a) If the <search condition> of some <searched when clause> in
12
a <case specification> is true, then the value of the <case
13
specification> is the value of the <result> of the first
14
(leftmost) <searched when clause> whose <search condition> is
15
true, cast as the data type of the <case specification>.
16
17
b) If no <search condition> in a <case specification> is true,
18
then the value of the <case expression> is the value of the
19
<result> of the explicit or implicit <else clause>, cast as
20
the data type of the <case specification>.
What are the most popular flow control functions in SQL?
The most popular flow control functions in SQL are:
How does the ELSE clause work in the CASE SQL expression?
The ELSE clause in the CASE WHEN SQL statement serves as a fallback option. If none of the WHEN conditions is true, CASE returns the expression specified in the ELSE clause. This mechanism provides a default action to be taken when none of the preceding conditions is met.
When does the SQL CASE operator return NULL?
The SQL CASE operator returns NULL in two conditions:
Are there any performance considerations to consider when using the CASE statement in SQL?
CASE as SQL command is a great tool for conditional logic, but using it excessively or adding long and complex conditions may impact performance. That is especially true when dealing with large datasets. Evaluate the query execution plan and consider alternative approaches—such as indexing or stored procedures—to optimize the performance of your queries.
