During many decision-making phases in programming code (conditional logic), there are times execution depends on several different factors. Multiple conditional tests are powerful and constraining, oftentimes requiring more than one test to be passed in order for program flow to proceed.
For MySQL (and standard SQL in general) the CASE expression is used for IF/THEN/ELSE conditional logic. The post, MySQL Simple CASE Expression – with examples, covered Simple CASE queries which are essentially equality tests. MySQL Simple CASE is but one variant of 2, with the other being a MySQL Searched CASE Expression. A MySQL Searched CASE Expression can have multiple conditional tests in each WHEN clause. These conditional tests can be AND/OR comparisons, BETWEEN value range tests, subqueries, IN() operators, and more. Continue reading to see examples of the MySQL Searched CASE Expression…
OS and DB used:
- Linux Mint 20 “Ulyana”
- MySQL 8.0.24
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!
As in the post about Simple CASE expressions, I am using this table and data 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)
MySQL Searched CASE Expression: Syntax and similarities compared to the Simple CASE Expression
Syntax-wise, the MySQL searched CASE expression is similar to the Simple CASE with the exception that no column or expression is listed immediately following the CASE keyword as is with Simple CASE. Other than that, the structure is the same between the two. However, they are very far from being one and the same. The key difference is that Searched CASE allows multiple comparisons in any of the WHEN clauses.
While the example queries that follow are arbitrary and likely not very meaningful, my goal is to provide a foundational understanding of how you might use a Searched CASE expression. I would be remiss if I said these are the only ways you can use Searched CASE for its use goes far beyond what I can cover in this blog post.
The Searched CASE expression can be more complex than Simple CASE, having multiple different types of conditions in the WHEN clause:
CASE WHEN country = 'USA' AND state = 'k' THEN 'Unknown US State'
WHEN country = 'CAN' AND state IS NULL THEN 'Unknown Canada State'
WHEN country = 'MEX' AND state IS NULL THEN 'Unknown MEX state'
ELSE 'Not sure state'
END AS country_state_description
FROM friends;
+---------+-------+---------------------------+
| country | state | country_state_description |
+---------+-------+---------------------------+
| USA | k | Unknown US State |
| CAN | NULL | Unknown Canada State |
| USA | k | Unknown US State |
| ITA | k | Not sure state |
| USA | k | Unknown US State |
| USA | k | Unknown US State |
| MEX | NULL | Unknown MEX state |
+---------+-------+---------------------------+
7 rows in set (0.0011 sec)
Where the Simple CASE only permits an equality comparison, notice in the Searched CASE example, the presence of the AND logical operator, allowing multiple conditions to be evaluated.
MySQL Searched CASE Expression: Multiple conditionals and comparisons
In this next query, notice the presence of the greater than (>) and less than (<) comparison operators in addition to the equality test on the ‘country’ column value:
CASE
WHEN country = 'USA' AND age < 45 THEN 'Not mid 40''s'
WHEN country = 'USA' AND age > 45 THEN 'Mid 40''s'
WHEN country = 'MEX' OR country = 'ITA' AND age > 45 THEN 'Neighbor mid 40''s'
ELSE 'Not sure at this time'
END AS age_description
FROM friends;
+------------+-----------+-----+---------+-----------------------+
| first_name | last_name | age | country | age_description |
+------------+-----------+-----+---------+-----------------------+
| Max | Maxer | 44 | USA | Not mid 40's |
| Mary | Murphy | 41 | CAN | Not sure at this time |
| Charlie | Charles | 48 | USA | Mid 40's |
| Humpty | Dumpty | 48 | ITA | Neighbor mid 40's |
| Roger | Dodger | 44 | USA | Not mid 40's |
| Jim | Russ | 44 | USA | Not mid 40's |
| Jupyter | Moonbeam | 41 | MEX | Neighbor mid 40's |
+------------+-----------+-----+---------+-----------------------+
7 rows in set (0.0202 sec)
Consider making a small donation on my behalf as I continue to provide valuable content here on my blog. Thank you!
You can even use the IN() operator for Searched CASE expression comparisons in any WHEN clause. I have changed the above query and now included IN ('MEX','ITA') instead of the OR logical operator as before:
CASE
WHEN country = 'USA' AND age < 45 THEN 'Not mid 40''s'
WHEN country = 'USA' AND age > 45 THEN 'Mid 40''s'
WHEN country IN ('MEX','ITA') AND age > 45 THEN 'Neighbor mid 40''s'
ELSE 'Not sure at this time'
END AS age_description
FROM friends;
+------------+-----------+-----+---------+-----------------------+
| first_name | last_name | age | country | age_description |
+------------+-----------+-----+---------+-----------------------+
| Max | Maxer | 44 | USA | Not mid 40's |
| Mary | Murphy | 41 | CAN | Not sure at this time |
| Charlie | Charles | 48 | USA | Mid 40's |
| Humpty | Dumpty | 48 | ITA | Neighbor mid 40's |
| Roger | Dodger | 44 | USA | Not mid 40's |
| Jim | Russ | 44 | USA | Not mid 40's |
| Jupyter | Moonbeam | 41 | MEX | Not sure at this time |
+------------+-----------+-----+---------+-----------------------+
7 rows in set (0.0014 sec)
MySQL Searched CASE Expression: Dynamic-like queries
Searched CASE can make queries dynamic in a sense. Or as much as SQL can be dynamic. Again, these examples are not that useful but, will get you to thinking of ways you can use Searched CASE. On a row-by-row basis, imagine for any age over 45, you want to add 5 to it. For whatever reason maybe you are sending your friends over the 50-year-old mark early!
That is easy as can be. Simply use Searched CASE in the row value calculation to add the applicable value to the ‘age’ column value, dynamically:
age + CASE WHEN age > 45 THEN 5 ELSE 0 END AS arb_add
FROM friends;
+-----+---------+
| age | arb_add |
+-----+---------+
| 44 | 44 |
| 41 | 41 |
| 48 | 53 |
| 48 | 53 |
| 44 | 44 |
| 44 | 44 |
| 41 | 41 |
+-----+---------+
7 rows in set (0.0012 sec)
In the above query results, notice for any age value that is true for the conditional expression age > 45 a value of 5 is added to that ‘age’ column value. If not, there is no change in the returned CASE expression value since essentially adding 0 (zero) – by way of the ELSE clause – to the ‘age’ value yields the current age column value. Searched CASE is incredibly powerful when used in contexts such as this.
As I have said in the post already, the examples provided using Searched CASE are purely arbitrary and likely have no real-world use but should provide you with an idea of how Searched CASE works and some things you can accomplish with it.
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.