CASE is a Control Flow statement that acts a lot like an IF-THEN-ELSE statement to choose a value based on the data. The CASE statement goes through conditions and returns a value when the first condition is met. So, once a condition is true, it will short circuit, thereby ignoring later clauses, and return the result. As we'll see in today's blog, it can be used to test for conditions as well as discrete values.
Basic Syntax
The CASE statement comes in two flavors: the first evaluates one or more conditions and returns the result for the first condition that is true. If no condition is true, the result after ELSE is returned, or NULL if there is no ELSE part:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
The second CASE syntax returns the result for the first value=compare_value comparison that is true. If no comparison is true, the result after ELSE is returned, or NULL if there is no ELSE part:
CASE compare_value
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Some Examples
To try out the CASE statement, we'll be writing some queries against the Sakila Sample Database using Navicat Premium. It's a powerful database development and administration tool that can simultaneously connect to most popular databases, including MySQL, MariaDB, MongoDB, SQL Server, Oracle, PostgreSQL, and SQLite databases. It's also compatible with many cloud databases like Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, Google Cloud and MongoDB Atlas.
Syntax 1
Here's a query that selects a list of movie titles, along with their release year and rental price:
We'll add a column that splits rental prices into three categories: "discount", "regular", and "premium". The price ranges are:
- less than $2.99
- between $2.99 and $4.99
- $4.99 and up
To help with the CASE statement, Navicat provides Code Snippets that you can simply drag & and drop into the SQL editor. Although you can create your own, Navicat comes with many standard SQL statements, including DDL and flow control statements. In fact, you'll find the CASE statement at the top of the Flow Control list:
After you place the code snippet into the editor, editable sections are color coded to help identify them. You can use the Tab key to move from one to the next.
Since the statements are generic in nature, you may have to modify it slightly to suit your particular database type. Here is the complete CASE statement and query for MySQL:
Syntax 2
The 2nd CASE syntax is ideal for testing discrete values against two or more conditions. For example, we could use it to add a target audience column based on the film rating:
Conclusion
In today's blog we learned how the SQL CASE Statement can be employed to choose a value based on the underlying data. Example SQL statements were written in Navicat Premium. It helps you code fast with Code Completion and customizable Code Snippets by getting suggestions for keywords and stripping the repetition from coding. You can try it for 14 days completely free of charge for evaluation purposes.
