Programming logic is foundational in any application or piece of software. Without it, software wouldn’t really do much of anything. Everything happens off of choice. In the end, some truthy or falsy value is what makes stuff work. For IF
/THEN
/ELSE
logic in standard SQL, there is the CASE
expression. There are 2 variations of the CASE
Expression: Simple and Searched. In this post, I cover the Simple MySQL CASE
expression with example queries…
OS and DB used:
- Linux Mint 20 “Ulyana”
- MySQL 8.0.23
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
How does the MySQL Simple CASE Expression Work?
A Simple CASE
expression is essentially an equality test. Specify a column or expression immediately following the CASE
keyword, and then a series of WHEN
/THEN
clauses. In each WHEN
/THEN
clause, a specified value is tested against the column or expression that follows CASE
. The THEN
clause portion maintains what value is returned when there is a match between the listed value in the WHEN
clause and the Simple CASE
comparison target.
I have this ‘friends’ table with some practice data I’ll use for the example queries:
+---------+------------+-----------+-------+--------------+------------+-----+
| country | first_name | last_name | state | phone_num | birthday | age |
+---------+------------+-----------+-------+--------------+------------+-----+
| USA | Max | Maxer | k | 398-392-5656 | 1975-01-23 | 44 |
| CAN | Mary | Murphy | NULL | 212-543-9420 | 1978-03-23 | 41 |
| USA | Charlie | Charles | k | 888-767-2323 | 1971-08-22 | 48 |
| ITA | Humpty | Dumpty | k | 118-257-7344 | 1971-11-22 | 48 |
| USA | Roger | Dodger | k | 234-767-3983 | 1975-08-22 | 44 |
| USA | Jim | Russ | k | 424-060-3875 | 1975-05-05 | 44 |
| MEX | Jupyter | Moonbeam | NULL | 198-654-2827 | 1978-07-22 | 41 |
+---------+------------+-----------+-------+--------------+------------+-----+
7 rows in set (0.0015 sec)
Simple CASE Expression example queries
Suppose for each ‘county’ column abbreviation, I want to display a descriptive direction in relation to the United States (my home country and location). Using a Simple CASE
, it is quite easy:
CASE country
WHEN 'USA' THEN 'Home'
WHEN 'CAN' THEN 'Northern Neighbor'
WHEN 'MEX' THEN 'Southern Neighbor'
ELSE 'Not a neighboring county'
END AS direction
FROM friends;
+---------+---------------------------+
| country | direction |
+---------+---------------------------+
| USA | Home |
| CAN | Northern Neighbor |
| USA | Home |
| ITA | Not a neighboring country |
| USA | Home |
| USA | Home |
| MEX | Southern Neighbor |
+---------+---------------------------+
7 rows in set (0.0010 sec)
(Informational: Aliasing the CASE
expression using the AS
keyword is optional.)
Although 4 distinct values exist in the ‘country’ column, only 3 actually have a WHEN
/THEN
clause in the CASE
expression. By comparing ‘USA’, ‘CAN’, and ‘MEX’ to the ‘country’ column, any other row that does not match one of those 3 values, returns the expression in the ELSE
clause, ‘Not a neighboring country’.
MySQL Simple CASE Expression: The ELSE clause
You can omit the optional ELSE
clause in a CASE
expression. Any WHEN
/THEN
clause that does not have a matching value, returns NULL
:
WHEN 'USA' THEN 'United States of America'
WHEN 'CAN' THEN 'Canada'
WHEN 'MEX' THEN 'Mexico'
END AS full_country_name
FROM friends;
+---------+--------------------------+
| country | full_country_name |
+---------+--------------------------+
| USA | United States of America |
| CAN | Canada |
| USA | United States of America |
| ITA | NULL |
| USA | United States of America |
| USA | United States of America |
| MEX | Mexico |
+---------+--------------------------+
7 rows in set (0.0218 sec)
Consider making a small donation on my behalf as I continue to provide valuable content here on my blog. Thank you!
MySQL Simple CASE Expression: CASE matches the first TRUE expression
MySQL Simple CASE
expression matches the first expression that is TRUE
. As shown in the next query, there are 2 possible THEN
clause return values for ‘CAN’: ‘Canadian’ and ‘Canada’. Notice in the query results, ‘Canadian’ is returned for the ‘CAN’ value since it is the first match that evaluates to TRUE
:
WHEN 'USA' THEN 'United States of America'
WHEN 'CAN' THEN 'Canadian'
WHEN 'CAN' THEN 'Canada'
WHEN 'MEX' THEN 'Mexico'
ELSE 'Not sure'
END AS full_country_name
FROM friends;
+---------+--------------------------+
| country | full_country_name |
+---------+--------------------------+
| USA | United States of America |
| CAN | Canadian |
| USA | United States of America |
| ITA | Not sure |
| USA | United States of America |
| USA | United States of America |
| MEX | Mexico |
+---------+--------------------------+
7 rows in set (0.0009 sec)
Visit the official online documentation on the CASE Expression for more information.
I mentioned in the opening portion of this post that there are 2 forms of the CASE
expression. The Searched CASE
expression variant is more powerful than its Simple version counterpart. I plan on publishing a follow-up blog post on Searched CASE
so be sure and subscribe to my blog for notifications when that post is released. Thanks for reading!!!
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the βClick To Subscribe!β button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
2 thoughts on “MySQL Simple CASE Expression – with examples”