Case statement in MySQL

sql

case-statement

mysql-functions

sql-queries

byAnton Shumikhin·Sep 5, 2024

The CASE statement in MySQL allows for conditional logic in SQL queries, attributing values according to specific criteria:

SELECT employee_id,
       CASE
           WHEN salary < 3000 THEN 'Entry level' // Duty calls! Can't afford coffee ☕
           WHEN salary <= 7000 THEN 'Mid level'  // Upgraded to chai latte 🍵
           ELSE 'Senior level'                   // Sipping on a cappucino by the window ☕🌤️
       END AS experience_level
FROM employees;

This request returns each employee_id along with an experience_level label sorted by their salary.

Portability and MySQL-specific Functions

Keeping to standard SQL syntax, such as the CASE statement, ensures portability across various SQL databases. Although MySQL-specific functions like IF() might seem more straightforward or readable, using CASE guarantees a smoother transition between SQL systems.

Here's how IF() simplifies the same query:

SELECT employee_id,
       IF(salary < 3000, 'Entry level',   //🅱️eginner mode
          IF(salary <= 7000, 'Mid level',  // 🆙 Upgrade!
            'Senior level')) AS experience_level // 💰 Rolling in dough!
FROM employees;

Take it for a spin! Explore different data scenarios to ensure accuracy, and you'll find CASE could do the heavy lifting in complex conditions quite efficiently.

Unmatched conditions and default values

Defining a default case using ELSE in a CASE statement ensures all rows receive a value assignment. If no conditions are met, the default value ensures no row gets left behind!

SELECT product_id,
       CASE
           WHEN quantity > 50 THEN 'Bulk stock'
           WHEN quantity > 10 THEN 'Sufficient stock'
           ELSE 'Low stock' // Danger! Danger! Low inventory detected! 🚨
       END AS stock_status
FROM products;

This guards against null values in critical fields which could turn your otherwise fun data extraction experience into a debug saga!

CASE in Action: Peeling Back the Layers

Mixing Conditions with AND, OR and More!

CASE statements don't shy away from a good mix! Here's how they navigate multiple conditions, nested CASE statements, or logical operators like AND, OR:

SELECT customer_id,
       CASE
         WHEN age < 20 AND student = 'yes' THEN 'Young student discount' // Early bird special
         WHEN age BETWEEN 20 AND 65 THEN 'Standard pricing' // Alas, the burden of adulting
         ELSE 'Senior citizen discount' // Silver surfer's delight!
       END AS pricing_tier
FROM customers;

Non-Match? Bring in the zeroes!

To bypass NULL landmines, assign a default numeric value like 0 when no conditions fulfill:

SELECT product_id,
       CASE
           WHEN is_discounted THEN discount_value // Markdown baby!
           ELSE 0 // Regular is too main-stream 😌
       END AS discount_applied
FROM products;

The Special Case of NULL

Be wary! In a CASE statement, MySQL treats NULL as unmatched in any WHEN clause. Tread with caution:

SELECT user_id,
       CASE
           WHEN last_login_date IS NULL THEN 'Never logged in' // Where art thou?
           ELSE 'Active user' // Hallo! Welcome back!
       END AS user_status
FROM users;