Conditional Logic with CASE Statements in SQL

Table of Contents

1. Introduction
2. What is a CASE Statement?
3. Syntax of CASE Statement
4. Simple CASE vs Searched CASE
5. Using CASE in SELECT
6. Using CASE in ORDER BY
7. Using CASE in WHERE
8. Nested CASE Statements
9. Using CASE with Aggregates
10. Combining CASE with GROUP BY
11. Real-World Example: Categorizing Salaries
12. Real-World Example: Grading System
13. Formatting Output with CASE
14. Handling NULLs with CASE
15. CASE with JOIN Conditions
16. Limitations of CASE
17. CASE vs IF vs IIF
18. Common Errors in CASE Usage
19. Best Practices for Writing CASE
20. Summary and What’s Next

1. Introduction

The CASE statement is SQL’s way of implementing conditional logic — similar to if-else statements in programming. It allows you to return different values or apply different logic depending on specific conditions.

2. What is a CASE Statement?

A CASE statement evaluates conditions and returns a value based on the first condition that is true. It’s used to transform values, categorize data, or apply logic directly in queries.

3. Syntax of CASE Statement

Searched CASE:

sqlCopyEditCASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

Simple CASE:

sqlCopyEditCASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

4. Simple CASE vs Searched CASE

5. Using CASE in SELECT

sqlCopyEditSELECT name,
  CASE 
    WHEN salary > 100000 THEN 'High'
    WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_band
FROM employees;

Categorizes employees based on salary.

6. Using CASE in ORDER BY

sqlCopyEditSELECT name, status
FROM customers
ORDER BY 
  CASE 
    WHEN status = 'Premium' THEN 1
    WHEN status = 'Standard' THEN 2
    ELSE 3
  END;

Custom sort order based on business logic.

7. Using CASE in WHERE

Not common but possible:

sqlCopyEditSELECT * 
FROM orders
WHERE 
  CASE 
    WHEN priority = 'High' THEN shipped = 'Yes'
    ELSE 1=1
  END;

Often better to use standard WHERE + OR clauses for readability.

8. Nested CASE Statements

sqlCopyEditSELECT name,
  CASE 
    WHEN role = 'Manager' THEN 
      CASE 
        WHEN department = 'HR' THEN 'HR Manager'
        ELSE 'Manager'
      END
    ELSE 'Employee'
  END AS title
FROM staff;

Useful for multi-layered logic.

9. Using CASE with Aggregates

sqlCopyEditSELECT 
  COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS males,
  COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS females
FROM users;

Conditional counting using CASE inside aggregate functions.

10. Combining CASE with GROUP BY

sqlCopyEditSELECT 
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group,
  COUNT(*) AS total
FROM citizens
GROUP BY 
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 65 THEN 'Adult'
    ELSE 'Senior'
  END;

Group data by dynamically derived categories.

11. Real-World Example: Categorizing Salaries

sqlCopyEditSELECT department,
  COUNT(*) AS total,
  SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department;

12. Real-World Example: Grading System

sqlCopyEditSELECT name,
  CASE 
    WHEN marks >= 90 THEN 'A'
    WHEN marks >= 75 THEN 'B'
    WHEN marks >= 60 THEN 'C'
    ELSE 'D'
  END AS grade
FROM students;

Transforms numeric scores into letter grades.

13. Formatting Output with CASE

sqlCopyEditSELECT name,
  CASE 
    WHEN status IS NULL THEN 'Unknown'
    ELSE status
  END AS formatted_status
FROM users;

Used to clean up NULLs or inconsistent data.

14. Handling NULLs with CASE

sqlCopyEditSELECT name,
  CASE 
    WHEN email IS NULL THEN 'Email Missing'
    ELSE email
  END AS contact_email
FROM customers;

Always consider NULL explicitly if necessary.

15. CASE with JOIN Conditions

sqlCopyEditSELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = 
  CASE 
    WHEN e.role = 'Intern' THEN NULL
    ELSE d.id
  END;

Avoid complex logic in JOIN conditions unless needed.

16. Limitations of CASE

• Cannot return multiple columns
• Cannot perform actions (e.g., DML like INSERT)
• Complex nesting can reduce readability
• Cannot be used outside of SQL statements (not procedural)

17. CASE vs IF vs IIF

18. Common Errors in CASE Usage

19. Best Practices for Writing CASE

• Use searched CASE for complex logic
• Always include an ELSE for unexpected values
• Alias the CASE result with meaningful names
• Use indentation for readability
• Avoid nesting more than two levels deep

20. Summary and What’s Next

The CASE statement is SQL’s powerful conditional logic tool, allowing you to transform, categorize, and compute values dynamically. Whether you’re cleaning data, computing derived columns, or customizing output, mastering CASE enhances the flexibility of your queries.