PostgreSQL CASE

Key CASE Concepts

CASE status
  WHEN 'active' THEN 'Active'
  WHEN 'inactive' THEN 'Inactive'
END

CASE
  WHEN age < 18 THEN 'Minor'
  WHEN age >= 18 THEN 'Adult'
END

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

CASE
  WHEN type = 'A' THEN
    CASE WHEN qty > 10 THEN 'High'
    ELSE 'Low' END
END

🔹 Simple CASE Statement

The simple CASE statement compares a single expression against multiple values. It's useful when you need to match exact values and return corresponding results. This is the most straightforward form of CASE and works well for status codes, categories, or any discrete values.

-- Categorize products by type
SELECT product_name,
    product_type,
    CASE product_type
        WHEN 'electronics' THEN 'Tech Department'
        WHEN 'clothing' THEN 'Fashion Department'
        WHEN 'food' THEN 'Grocery Department'
        ELSE 'General Department'
    END AS department
FROM products;

🔹 Searched CASE Statement

The searched CASE statement evaluates multiple boolean conditions in order. It's more flexible than simple CASE because you can use different columns and operators in each condition. This is perfect for range checks, complex logic, and when conditions involve multiple columns or calculations.

-- Grade students based on scores
SELECT student_name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

🔹 CASE in WHERE Clause

You can use CASE statements in WHERE clauses to create dynamic filtering conditions. This allows you to apply different filter logic based on certain conditions, making your queries more adaptable. It's particularly useful when you need conditional filtering based on user input or other variables in your application.

-- Filter based on priority
SELECT order_id, status, priority
FROM orders
WHERE 
    CASE
        WHEN priority = 'high' THEN status = 'pending'
        WHEN priority = 'low' THEN status IN ('pending', 'processing')
        ELSE TRUE
    END;

🔹 CASE in ORDER BY

Using CASE in ORDER BY clauses lets you create custom sorting logic. You can prioritize certain values or create complex sorting rules that go beyond simple ascending or descending order. This is helpful when you need business-specific sorting, like showing urgent items first regardless of date.

-- Custom sorting order
SELECT task_name, priority
FROM tasks
ORDER BY
    CASE priority
        WHEN 'urgent' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END;

🔹 CASE with Aggregate Functions

Combining CASE with aggregate functions like COUNT, SUM, or AVG allows you to perform conditional aggregations. This is powerful for creating pivot-like reports or calculating different metrics based on conditions. You can count or sum only the rows that meet specific criteria within a single query.

-- Count orders by status
SELECT 
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders;

🔹 Nested CASE Statements

Nested CASE statements allow you to create multi-level conditional logic by placing one CASE inside another. This is useful for complex business rules that require checking multiple conditions in a hierarchical manner. While powerful, use nesting sparingly to keep queries readable and maintainable for your team.

-- Complex categorization
SELECT employee_name,
    department,
    salary,
    CASE department
        WHEN 'Sales' THEN
            CASE
                WHEN salary > 50000 THEN 'Senior Sales'
                ELSE 'Junior Sales'
            END
        WHEN 'IT' THEN
            CASE
                WHEN salary > 60000 THEN 'Senior Developer'
                ELSE 'Junior Developer'
            END
        ELSE 'Other'
    END AS job_level
FROM employees;