CASE EXPRESSIONS

CASE expressions allow SQL users to implement conditional logic similar to the ubiquitous IF…THEN…ELSE form found in other programming languages such as python, java, and c+ among others. SQL’s CASE expressions come in two forms, simple and searched. The focus of this page is to explore each of these forms through real-world problems.

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the Simple CASE Expressions section

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Simple CASE Expressions
This case expression form allows users little flexibility in terms of the expressions used and the condition being evaluated. The simple case expression allows users to compare an expression to another expression in terms of equality. The syntax can be found below.

CASE expression_1
    WHEN comparison_expression_1
        THEN returned_expression_1
    WHEN comparison_expression_2
        THEN returned_expression_2
    ELSE catch_all_expression
END AS attribute_alias

expression_1 represents a value for a specific row for a specific pre-existing attribute. comparison_expression_1 represents the value for which expression_1 is compared. If expression_1 equals comparison_expression_1, then returned_expression_1 is returned for the row. If the condition captured in the first WHEN and comparison_expression pair does not evaluate to TRUE, then expression_1 is compared to the expression following the subsequent WHEN. In the code above, that is comparison_expression_2. This process continues for each specified WHEN and comparison_expression pair. If none of the specified pairs evaluate to TRUE, then NULL is returned, by default. This default behavior can be overridden by explicitly stating a catch_all_expression preceded by the ELSE keyword.

Let us put this simple case expression to work. Suppose the data stored for each customer contains a gender attribute specifying the customer’s gender. Currently, the possible values are M, F, and NULL; however, for the purposes of generating a report containing the number of customers per unique gender value, you would like to replace the values with Male, Female, and Not Specified, respectively. Using a simple case expression, each row’s gender value can be compared to a specific value for equality. When the two values are equal, the corresponding value following the THEN keyword replaces the original. For example, when a row’s gender value is “M,” then the value returned is “Male.” For clarity, see the code and corresponding output below.

SELECT 
    CASE oe.customers.gender
    	WHEN 'M'
    		THEN 'Male'
    	WHEN 'F'
    		THEN 'Female'
    	ELSE 'Not Specified'
    END AS gender,
    COUNT(*) AS customer_count
FROM
	oe.customers
GROUP BY
    CASE oe.customers.gender
    	WHEN 'M'
    		THEN 'Male'
    	WHEN 'F'
    		THEN 'Female'
    	ELSE 'Not Specified'
    END;

Searched CASE Expressions
I can count on both hands the number of times I have made use of simple case expressions to solve real-world problems. I tend to use searched case expressions out of habit and necessity. Searched case expressions provide users more flexibility in terms of developing conditional logic. Each WHEN…THEN branch can contain a wide-variety of comparison operators, including, among others, =, !=, >, >=, and <=. Additionally, each branch can contain expressions other than pre-existing attributes and constants. Review the syntax for searched case expressions below, and then let dive into a few real-world problems.

CASE
    WHEN condition_1
        THEN returned_expression_1
    WHEN condition_2
        THEN returned_expression_2
    ELSE catch_all_expression
END AS attribute_alias

For the first real-world problem, consider the classic programming problem where the goal is to programmatically classify integer values as even or odd. In the code below, a recursive common table expression is used to generate the integer values one through ten. Subsequently, a searched case expression consisting of two branches is used to capture whether the integer value is even or odd. The first branch is responsible for determining if the integer is even and the second used to return a catch-all value of “odd” when the first branch’s condition evaluates to FALSE. In the first branch, the MOD function is used to calculate the remainder when each integer value is divided by two. If the remainder is zero, then the value must be even. If the value is not even, then it must be odd. Hence, the ELSE keyword being followed by the catch-all value of “odd.”

WITH integer_sequence (integer_value) AS (
    SELECT
        1 AS integer_value 
    FROM
        dual
    UNION ALL
    SELECT
        integer_value + 1
    FROM
        integer_sequence
    WHERE
        integer_sequence.integer_value < 10
)
SELECT
    integer_sequence.integer_value,
    CASE
        WHEN MOD(integer_sequence.integer_value, 2) = 0
            THEN 'Even'
        WHEN MOD(integer_sequence.integer_value, 2) = 1
    		THEN 'Odd'
    	ELSE 'N/A'
    END AS number_category
FROM
    integer_sequence;

If you are unfamiliar with the concept of recursion and how it can be used to solve other, real-world problems, then consider reading my page dedicated to recursion here.

Despite the problem above being a classic, it may not ring “real-world” to you. Let us explore another real-world problem that undoubtedly sufficiently highlights the usefulness of CASE expressions.

Suppose you are tasked with generating a report containing the details corresponding to each order. The necessary attributes are order ID, order date, order mode, customer ID, order status, and order total. In addition to those attributes, you are required to include an attribute categorizing each order based on the order total value and the range the value falls within. Management has provided the following ranges:

0-10k
10-25k
25-50k
50-75k
75-100k
100k+

A searched case expression can be used to categorize each order total value into one of the ranges above. In the code below, each order total value is compared against constant values (e.g., 10000, 25000, etc.) serving as non-inclusive cutoffs. For example, if the order total is less than $10,000, then the order total category value for the specific order will read as “0-10k.” If the order total value is not less than $10,000, then the first WHEN branch will evaluate to FALSE and the categorization process will continue to each subsequent branch until one of the WHEN branches evaluates to TRUE or the ELSE branch is reached and the order total category takes on the catch-all value of “100k+.” Keep in mind the branches are evaluated in the order they are written, so it is important to order the branches in a sensible way. In this scenario, to avoid categorizing an order total value incorrectly, ensure the constant values across the branches are in ascending or descending order.

SELECT
    oe.orders.order_id,
    oe.orders.order_date,
    oe.orders.order_mode,
    oe.orders.customer_id,
    oe.orders.order_status,
    oe.orders.order_total,
    CASE
        WHEN oe.orders.order_total < 10000
            THEN '0-10k'
        WHEN oe.orders.order_total < 25000
            THEN '10-25k'
        WHEN oe.orders.order_total < 50000
            THEN '25-50k'
        WHEN oe.orders.order_total < 75000
            THEN '50-75k'
        WHEN oe.orders.order_total < 100000
            THEN '75-100k'
        ELSE '100k+'
    END AS order_total_category
FROM
    oe.orders;

For the final real-world problem, I want to highlight how CASE expressions can be used with aggregate functions to perform a powerful transformation commonly known as pivoting rows to attributes or columns. Using the orders table, suppose you are tasked with generating a report containing one row per customer. Within each row, the corresponding customer’s order total values need to be bucketed into the same order total categories specified in the previous problem; however, this report requires the order total category values to be represented as attributes or columns (e.g., 0-10k, 10-25k, etc.). The values within the corresponding row and column are to represent the number of orders the customer placed within the corresponding order total category. For clarity, you are attempting to generate the output below. The first row reads as, “customer ID 107 placed one order in the 0-10k range, zero in 10-25k range, one in the 25-50k range, two in the 50-75k range, zero in the 75-100k range, and zero in the 100k+ range.” Review the output below and then let us take a look at the code required to generate the desired output.

In the code below, the GROUP BY clause groups each customer’s orders. From these groups, the customer ID value is selected as the single non-aggregate attribute in the SELECT clause. The remainder are derived attributes making use of the SUM function. Each of these derived attributes include a CASE expression nested within to determine the number of orders the corresponding customer placed falling within a specific order total category. The CASE expression’s conditions are evaluated on each of the rows or orders corresponding to each customer (i.e., the grouped rows). For each order meeting the condition specified in the initial WHEN branch, a value of one is recorded. Otherwise, a zero is recorded. After evaluating the CASE expression’s conditions for each of the customer’s orders, the individual results are summed up. This sum represents the total number of orders the customer placed within the order total category represented by the attribute’s alias (e.g., 0-10k) and driven by the conditions evaluated in the CASE expression.

SELECT
    oe.orders.customer_id,
    SUM(
        CASE
            WHEN oe.orders.order_total < 10000
                THEN 1
            ELSE 0
        END
    ) AS "0-10k",
    SUM(
        CASE
            WHEN oe.orders.order_total >= 10000
                AND oe.orders.order_total < 25000
                THEN 1
            ELSE 0
        END
    ) AS "10-25k",
    SUM(
        CASE
            WHEN oe.orders.order_total >= 25000
                AND oe.orders.order_total < 50000
                THEN 1
            ELSE 0
        END
    ) AS "25-50k",
    SUM(
        CASE
            WHEN oe.orders.order_total >= 50000
                AND oe.orders.order_total < 75000
                THEN 1
            ELSE 0
        END
    ) AS "50-75k",
    SUM(
        CASE
            WHEN oe.orders.order_total >= 75000
                AND oe.orders.order_total < 100000
                THEN 1
            ELSE 0
        END
    ) AS "75-100k",
    SUM(
        CASE
            WHEN oe.orders.order_total >= 100000
                THEN 1
            ELSE 0
        END
    ) AS "100k+"
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id;

If you are interested in seeing the role CASE expressions can play when creating data visualizations, consider checking out a brief example on my Tableau Public page here.

Rewriting Simple CASE Expressions
Any simple case expression can be rewritten as a searched case expression, including the one featured in the first example on this page. The equivalent can be found below.

CASE
    WHEN oe.customers.gender = 'M'
        THEN 'Male'
    WHEN oe.customers.gender = 'F'
        THEN 'Female'
    ELSE 'Not Specified'
END AS gender

Additionally, some searched case expressions can be rewritten as simple case expressions, including the one used in a previous example above to classify integers as odd or even. The equivalent can be found below.

CASE MOD(integer_sequence.integer_value, 2)
    WHEN 0
        THEN 'Even'
    WHEN 1
        THEN 'Odd'
    ELSE 'N/A'
END AS number_category

Short-circuit Evaluation
For both types of CASE expressions, it is important to keep in mind the branches are traversed in the order they are written. When you are building a CASE expression consisting of multiple branches, consider placing the branch with the condition most likely to evaluate to TRUE first because subsequent branches are not traversed when a previous branch evaluates to TRUE for a row. By doing so, you can ensure the minimal amount of branches and corresponding conditions are evaluated.

Knowledge Check
If you’d like to confirm your understanding on the use of CASE expressions and other fundamental concepts, consider solving a handful of my practice problems here.