SQL — Case

What is CASE?

CASE is SQL's if-else logic. It evaluates conditions and returns a value based on which condition is true.

Basic Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Simple Example

SELECT
  first_name,
  salary,
  CASE
    WHEN salary > 100000 THEN 'High'
    WHEN salary > 70000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_band
FROM employees;

CASE in SELECT

Create calculated columns:

SELECT
  product_name,
  price,
  CASE
    WHEN price < 10 THEN 'Budget'
    WHEN price < 50 THEN 'Mid-range'
    WHEN price < 100 THEN 'Premium'
    ELSE 'Luxury'
  END AS category
FROM products;

CASE in ORDER BY

Custom sort orders:

SELECT order_id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'urgent' THEN 1
    WHEN 'pending' THEN 2
    WHEN 'shipped' THEN 3
    WHEN 'delivered' THEN 4
  END;

CASE in WHERE

SELECT *
FROM employees
WHERE
  CASE
    WHEN department = 'Sales' THEN salary > 50000
    WHEN department = 'Engineering' THEN salary > 70000
    ELSE TRUE
  END;

Searched vs Simple CASE

Searched CASE (most flexible)

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  ELSE 'C'
END

Simple CASE (compare one value)

CASE department
  WHEN 'Sales' THEN 'Revenue Team'
  WHEN 'Engineering' THEN 'Tech Team'
  ELSE 'Other'
END

CASE with Aggregate Functions

Count by condition:

SELECT
  COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
  COUNT(CASE WHEN salary BETWEEN 50000 AND 100000 THEN 1 END) AS mid_earners,
  COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_earners
FROM employees;

CASE with UPDATE

UPDATE employees
SET bonus = CASE
  WHEN performance = 'excellent' THEN salary * 0.2
  WHEN performance = 'good' THEN salary * 0.1
  ELSE 0
END;

CASE with NULL

SELECT
  first_name,
  CASE
    WHEN phone IS NOT NULL THEN phone
    WHEN email IS NOT NULL THEN email
    ELSE 'No contact info'
  END AS contact
FROM customers;

Practical Example: Pivot Data

SELECT
  product_name,
  SUM(CASE WHEN QUARTER(order_date) = 1 THEN amount ELSE 0 END) AS q1,
  SUM(CASE WHEN QUARTER(order_date) = 2 THEN amount ELSE 0 END) AS q2,
  SUM(CASE WHEN QUARTER(order_date) = 3 THEN amount ELSE 0 END) AS q3,
  SUM(CASE WHEN QUARTER(order_date) = 4 THEN amount ELSE 0 END) AS q4
FROM orders
GROUP BY product_name;

Best Practices

• Always include an ELSE — even if it's just ELSE NULL
• Keep the logic readable — don't nest more than 3 levels
• Use CASE in ORDER BY for custom sort orders
• Use CASE with COUNT and SUM for conditional aggregation

Practice

1. Classify products into price tiers using CASE
2. Sort employees by custom priority (managers first, then by seniority)
3. Create a pivot table showing monthly sales by product
4. Write an UPDATE that applies different bonuses based on performance