SQL CASE Expressions and Conditional Logic

🔹 What Is a CASE Expression?

The CASE expression lets you embed IF-THEN-ELSE logic inside SQL statements. Use it to categorize data, calculate conditional values, or control ordering without leaving SQL.

⚙️ Syntax Variants

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

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

🧠 Example 1 – Categorizing Salaries

SELECT
  employee_name,
  salary,
  CASE
    WHEN salary >= 100000 THEN 'High Earner'
    WHEN salary BETWEEN 50000 AND 99999 THEN 'Mid Earner'
    ELSE 'Low Earner'
  END AS salary_category
FROM employees;

🧠 Example 2 – Conditional Aggregation

SELECT
  department_id,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department_id;

🧠 Example 3 – Custom Sorting

SELECT employee_name, job_title, department
FROM employees
ORDER BY CASE
  WHEN department = 'HR' THEN 1
  WHEN department = 'Finance' THEN 2
  ELSE 3
END;

🧠 Example 4 – Conditional Filters

SELECT *
FROM orders
WHERE CASE
  WHEN customer_type = 'VIP' THEN total_amount > 1000
  ELSE total_amount > 500
END;

🧠 Example 5 – CASE in UPDATE

UPDATE employees
SET bonus = CASE
  WHEN performance_rating = 'A' THEN 1000
  WHEN performance_rating = 'B' THEN 500
  ELSE 0
END;

⚖️ Best Practices

• Always include an ELSE clause to handle unexpected values.
• Keep logic readable—break overly complex CASE expressions into CTEs.
• Combine with window functions for advanced analytics.

💡 Interview Soundbite