intro
The MySQL CASE expression is one of the most popular expressions offered by MySQL. Here’s how to use it and what it does.
How many of you have ever seen a piece of SQL code written like this?
1
SELECT product_name, price,
2
CASE
3
WHEN stock_amount > 20 THEN "There are more than 20 of such items in stock"
4
WHEN stock_amount = 2 THEN "There are exactly 2 of such items in stock"
5
WHEN stock_amount = 3 THEN "There are exactly 3 of such items in stock"
6
ELSE "The quantity is less than 10"
7
END
8
FROM products;
“Hmm, rings a bell…”, — we hear some of you say. That’s the CASE
functionality within our beloved database management system, MySQL. The CASE
functionality within MySQL enables us to display a message when a certain condition is met.
The CASE Functionality within MySQL
Do you know what the code block above does? Its functionality is rather simple — it implements a WHEN ELSE
type of relationship where your database is enabled to display values in a table that don’t exist in itself after a certain condition is met.
Think of it this way — what could CASE WHEN stock_amount > 20 THEN...
mean? It means exactly what you think it does — we’ve told our database “when the stock amount is greater than 20, then display a value like this.” As simple as it can get.
When to Use the CASE Functionality in MySQL?
Developers revert to using the CASE
functionality available in MySQL and its counterparts (MariaDB and Percona Server) when:
Think of the CASE
clause in MySQL as a mini math helper — it will provide accurate, Excel-like calculations once it’s employed properly.
Limitations of CASE in MySQL
Be that as it may, as good as CASE
is, it does have limitations specific to itself.
The first thing you need to know is that you cannot have more than 255 expressions when using the function within MySQL, MariaDB, or Percona Server. That includes CASE
, WHEN
, THEN
, etc. — everything that makes the function tick.
Also keep in mind that many database management systems limits your actions by the size of the query — you can’t run a query bigger than x
bytes in size where x
is defined in the configuration file of your DBMS (my.cnf or other configuration files — look into max_allowed_packet
in MySQL and values having an equal meaning in other database management systems.)
The last thing we should mention is that there quite a lot of DBAs that like to “move around” the functionality available in database management systems — sometimes that also includes switching CASE
to a JOIN
query if we have a lot of records. In that case, keep in mind that if you “move results into a table” so that you use JOIN
instead of using CASE
, you are likely to observe functionality that is faster than running a simple CASE
query if you have a lot of records, but your queries may become confusing and troublesome to manage:
Also, keep in mind that JOIN
and INNER JOIN
offer exactly the same functionality and don’t differ much.
Going Beyond CASE in MySQL
For those of you who are reading this blog, the importance of things like INNER JOIN
or CASE
clauses should already be paramount; after all, you’re already using an SQL client with the best customer satisfaction rate in the market, right?
DbVisualizer is able to simplify your everyday DBA work by offering advanced query optimization capabilities, automatic ERD-like schema generation, automatically EXPLAIN
ing your queries and getting them ready to rock the performance stage of your database, and much more:
Grab a free trial of DbVisualizer today, and we’ll see you on the other side of database engineering — the winning side.
Summary
The CASE
clause in MySQL is a very powerful clause allowing us to perform mini Excel-like calculations within our database management system based on existing data and IF-THEN
-like clauses. Try using the CASE
clause today, explore our blog for more threads similar to this one, and until next time!
FAQ
What Does CASE Do?
The CASE
clause tells your database “when X is greater|lower|equal to Y, then display a message like this.”
When to Use CASE?
Use the CASE
clause whenever a need to perform simple Excel-like calculations arises using your database management system and the data set you have at hand at the moment.
Why Should I Use DbVisualizer?
DbVisualizer is the highest-rated SQL client in the market, it supports more than 50 database technologies, and it has more features than you can imagine. Grab a free trial now!