CASE
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL CASE Statement: Definitive Guide

intro

Find out why there are two different SQL CASE statements and learn how to use them for conditional branching logic in your queries.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

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:

  1. Simple CASE expression: Compares an expression to a set of expressions to determine the result.
  2. Searched CASE expression: Accepts a set of boolean expressions and returns the value contained in the first true expression.

Let’s see them both!

Simple CASE Expression

This is what the syntax of the first type of CASE as SQL command looks like:

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

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

Copy
        
1 'three'

Searched CASE Expression

The second CASE WHEN SQL expression has the syntax below:

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

Copy
        
1 SELECT 2 CASE 3 WHEN 4>1 THEN 'true' 4 ELSE 'false' 5 END;

It will return:

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

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

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

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

Note the values in the “score_grade” column
Note the values in the score_grade column

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:

  • For products in the “Electronics” category with a price greater than or equal to $1500, apply a 30% discount.
  • For products in the “Shoes” category, apply a 15% discount.
  • For products in the “Sports” category, apply a 5% discount.
  • For products in the “Gifts” category with a price less than or equal to $50, apply a 50% discount.
  • For all other products, maintain the original price.

Thanks to the CASE expression in SQL, you can apply those rules and get the updated prices with a single query as follows:

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

Note the automatically generated discounted prices
Note the automatically generated discounted prices

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:

  • For managers: If they have been with the company for 5 or more years, give them a 10% bonus on their salary. If they have been with the company for less than 5 years, give them a 5% bonus on their salary.
  • For developers: If they have been with the company for 3 or more years, give them an 8% bonus on their salary.
  • For analysts: If they have been with the company for 5 or more years, give them a 4% bonus on their salary. If they have been with the company for less than 5 years, give them a 3% bonus on their salary.
  • For sales employees: A 6% bonus of their salary is applied, regardless of the number of years with the company.
  • For employees in other roles: No bonus is applied.

You can generate the bonuses with the complex CASE SQL expression below:

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

Executing the SQL CAST query in DbVisualizer
Executing the SQL CAST query in DbVisualizer

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:

  • Master the two forms of the CASE statement. The first form is suitable when comparing a single expression with multiple values. The second form allows for more complex conditions.
  • Consult the documentation to see the casting rules when dealing with heterogeneous result expressions.
  • Assign meaningful aliases to the result columns of CASE as SQL statements for better query readability.
  • Maintain consistent indentation within your CASE SQL expressions to improve readability.
  • Conditions in WHEN branches do not need to be mutually exclusive, but avoiding conditional overlapping helps make your queries more maintainable.
  • In long queries, add comments to explain the logic behind each SQL CASE WHEN branch.
  • Take advantage of nested CASE WHEN SQL statements when dealing with complex conditions. Nesting helps create hierarchical logic within the statement, facilitating the conditional process.

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:

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

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

  • CASE: Used for conditional logic and branching within SQL queries.
  • COALESCE: Returns the first non-null value from a list of expressions. Find out more in our complete guide on SQL COALESCE.
  • NULLIF: Compares two expressions and returns null if they are equal, otherwise returns the first expression.

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:

  1. None of the specified WHEN branches evaluate to true and there is no ELSE branch.
  2. The WHEN condition that is true returns a NULL expression.

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02
title

SQL REPLACE Function: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-04-25
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

SQL EXCEPT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 7 min 2024-04-18

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗