CASE Statement in SQL

The CASE statement in SQL operates similar to an if-then-else control flow in most programming languages. It allows for conditional logic to be used in SQL queries, enabling the return of specific values or the execution of different expressions based on certain conditions.

For instance, you could use a CASE statement in a SELECT statement to change the display of data based on column values in each row.

Types of CASE Statements: Simple and Searched

There are two types of CASE expressions in SQL: simple and searched.

Simple CASE expression

The simple CASE expression compares an expression to a set of simple expressions to determine the result. It's like an equality check.

CASE expression
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    ELSE result
END;

Here is an example of a simple CASE statement:

SELECT
    customer_id,
    CASE country
        WHEN 'USA' THEN 'Domestic'
        ELSE 'International'
    END AS customer_type
FROM customers;

This statement is checking the country column for each row in the customers table. If the country is 'USA', it returns 'Domestic'. Otherwise, it returns 'International'. The new values are returned in a column named customer_type.

Searched CASE expression

The searched CASE expression evaluates a set of Boolean expressions to determine the result. It's like using boolean logic.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END;

Here is an example of a searched CASE statement:

SELECT
    customer_id,
    CASE
        WHEN total_purchases > 10000 THEN 'Premium'
        WHEN total_purchases > 5000 THEN 'Gold'
        ELSE 'Regular'
    END AS customer_category
FROM customers;

This statement checks the total_purchases column for each row in the customers table. If total_purchases is greater than 10,000, it returns 'Premium'. If total_purchases is greater than 5,000 (but not more than 10,000), it returns 'Gold'. For all other cases, it returns 'Regular'. The new values are returned in a column named customer_category.

Remember, in the searched CASE expression, the conditions are evaluated in the order they are written. If the first condition (total_purchases > 10000) is true, the CASE statement will return 'Premium' and will not evaluate the next condition (total_purchases > 5000).

CASE with ORDER BY

You can use a CASE statement within the ORDER BY clause to conditionally sort the output of your SQL queries. This powerful combination allows you to dynamically control the sort order based on specific criteria.

Here's an example of using a CASE statement within an ORDER BY clause:

SELECT
    customer_id,
    country,
    total_purchases
FROM
    customers
ORDER BY
    CASE
        WHEN country = 'USA' THEN total_purchases
        ELSE NULL
    END DESC;

In this example, the CASE statement sorts customers from the USA by their total purchases in descending order. Customers from other countries are not included in this sort and will appear at the end of the result set, as NULL values sort last when the order is DESC.

CASE with UPDATE

When used in an UPDATE statement, a CASE statement can provide conditional logic for the new values being set. This can be useful when the new value depends on the existing value in the row or the value in another column.

Here's an example of using a CASE statement within an UPDATE statement:

UPDATE
    customers
SET
    customer_category = CASE
        WHEN total_purchases > 10000 THEN 'Premium'
        WHEN total_purchases > 5000 THEN 'Gold'
        ELSE 'Regular'
    END;

In this example, the CASE statement updates the customer_category column based on the value in the total_purchases column for each row. Customers with total purchases over 10,000 are categorized as 'Premium', those with total purchases over 5,000 (but not more than 10,000) are 'Gold', and all others are 'Regular'.

Remember to use a WHERE clause in your UPDATE statements when you don't want to update every row in the table. For example, you may want to update only rows where customer_category is NULL.

UPDATE
    customers
SET
    customer_category = CASE
        WHEN total_purchases > 10000 THEN 'Premium'
        WHEN total_purchases > 5000 THEN 'Gold'
        ELSE 'Regular'
    END
WHERE
    customer_category IS NULL;

This prevents overwriting existing data in the customer_category column. In the above query, only those rows where customer_category is NULL will be updated.

Using NULL with CASE

In a CASE statement, NULL is handled differently than other values. If you test for a NULL value with an equality operator (like = or !=), the result is always NULL or unknown. To check for NULL, you should use the IS NULL and IS NOT NULL operators in your WHEN conditions.

Furthermore, if none of the conditions in the WHEN clauses are met and there is no ELSE clause, the CASE statement will return NULL.

Here's an example of using a CASE statement to replace NULL values:

SELECT
    customer_id,
    CASE
        WHEN email IS NULL THEN 'Email not provided'
        ELSE email
    END AS email
FROM
    customers;

In this example, the CASE statement checks if the email column is NULL for each row in the customers table. If it is, it returns 'Email not provided'. Otherwise, it returns the actual email.

Here's another example where CASE statement is used to replace NULL values with an average:

SELECT
    product_id,
    CASE
        WHEN price IS NULL THEN (SELECT AVG(price) FROM products WHERE price IS NOT NULL)
        ELSE price
    END AS price
FROM
    products;

In this example, the CASE statement checks if the price column is NULL for each row in the products table. If it is, it returns the average price of all non-NULL prices. Otherwise, it returns the actual price.