MySQL
SQL

When to Use CASE in MySQL?

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.

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

How many of you have ever seen a piece of SQL code written like this?

Copy
        
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;
SQL code block depicting how many items are in stock at the present moment
SQL code block depicting how many items are in stock at the present moment

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

  1. They have a decent chunk of rows in their table (think 1,000 or more.)
  2. They’re running a SELECT query.
  3. They need some quick, Excel-like calculations done with the result visible in a column.

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:

Switching CASE to INNER JOIN
Switching CASE to INNER JOIN

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 EXPLAINing your queries and getting them ready to rock the performance stage of your database, and much more:

Executing an EXPLAIN Plan in DbVisualizer
Executing an EXPLAIN Plan in DbVisualizer

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?

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

MySQL Backup and Recovery Best Practices: A Guide

author Lukas Vileikis tags BIG DATA MySQL OPTIMIZATION SECURITY SQL 7 min 2024-10-15
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

author Lukas Vileikis tags MySQL 5 min 2024-09-16
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09

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 ↗