Day 38: MySQL CASE Statements - Handling Conditional Logic in Queries!

Today, let us explore CASE statements in MySQL, a powerful tool for adding conditional logic directly within SQL queries!

?A CASE statement works like an IF-ELSE condition inside SQL

? It allows us to evaluate conditions and return specific values based on the results

1. Simple CASE Statement

? Compares a column against multiple values

? Returns a specific result when a match is found

Example:

SELECT name, job_title,
CASE job_tite 
     WHEN 'Manager' THEN 'Leadership'
      WHEN 'Developer' THEN 'Technical'
      WHEN 'HR' THEN 'Administrative'
       ELSE 'other'
END AS department
FROM employees;        

(Returns a custom department name based on job title.)

2. Searched CASE Statement

? Evaluates multiple conditions instead of just matching a single value

? More flexible than a simple CASE statement

Example: Classifying Customers Based on Spending

SELECT name, total_spent,
   CASE
       WHEN total_spent > 5000 THEN 'Premium Customer'
        WHEN total_spent BETWEEN 2000 AND 5000 THEN 'Regular Customer'
        ELSE 'New Customer'
   END AS customer_category
FROM customers;        

(Classifies customers into different spending categories.)

3. Using CASE with Aggregations

Example: Counting Orders by Payment Status

SELECT 
    COUNT(CASE WHEN payment_status = 'Paid' THEN 1 END) AS Paid_Orders,
    COUNT(CASE WHEN payment_status = 'Pending' THEN 1 END) AS Pending_Orders,
    COUNT(CASE WORD WHEN payment_status = 'Cancelled' THEN 1 END) AS                Cancelled_Orders FROM orders;        

(Returns counts of orders in different payment statuses.)

4. Using CASE in ORDER BY

Example: Sorting Employees Based on Experience Levels

SELECT name, experience_years
FROM employees
ORDER BY 
    CASE
         WHEN experience_years > 10 THEN 1
         WHEN experience_years BETWEEN 5 AND 10 THEN 2
         ELSE 3
     END;        

(Sorts employees by experience level, with the most experienced first)

Why Use CASE in MySQL?

? Adds conditional logic without modifying the database

? Improves readability of queries

? Reduces complexity by avoiding multiple queries

#100DaysCode #MySQL #SQL #Database #DataAnalysis #Learning #Python #BackendDevelopment