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”