Conditional Expressions: CASE WHEN ... THEN ... ELSE ... END

Chances are, you’ve already seen conditional expressions in programming languages: if-else, switch-case, and stuff like that. SQL has its own tool for working with conditions: the CASE expression. It lets you make decisions right in your queries: if a condition is true, do one thing; if not, do something else.

The CASE construct is especially handy when you’re dealing with data that might have NULL values. The syntax is super simple, like a cheat sheet—check it out:

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

Makes sense, right? "If it’s like this, do that, otherwise do something else." Easy to remember: "WHEN is if, THEN is what to do, ELSE is what to do if nothing matched."

Example: Classifying Products

Imagine we have a products table with a price column, and we want to group products based on their price.

SELECT
    name AS product_name,
    price,
    CASE
        WHEN price IS NULL THEN 'Unknown'
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 100 THEN 'Standard'
        ELSE 'Premium'
    END AS price_category
FROM products;

What’s going on here?

1. If the product’s price is missing (NULL), we show the category as 'Unknown'.
2. If the price is less than 50, the product is considered 'Budget'.
3. If the price is between 50 and 100, it goes into the 'Standard' category.
4. Everything else is a premium product 'Premium'.

Here’s the result:

SQL is like a wizard here, reading each row from the products table and cleverly classifying them.

Working with NULL in CASE WHEN

It’s super common to have missing values in your data (hey there, NULL), and you’ll want to swap them out for something else. For example, let’s say we have a users table with an email column, and for users without an email, we want to show 'Not Provided'.

SELECT
    user_id,
    name,
    CASE
        WHEN email IS NULL THEN 'Not Provided'
        ELSE email
    END AS email_address
FROM users;

SQL here works like a shaman, bringing half-empty rows to life. If the email is missing, it swaps in 'Not Provided'. If there’s an email—it just leaves it as is.

Conditional Expressions with Numbers

Sometimes you need more than just swapping values—you want to build new logic. For example, let’s say we have a students table with score and name columns.

We want to grade students as "Excellent", "Pass", and "Fail" depending on their scores.

SELECT
    name AS student_name,
    score,
    CASE
        WHEN score IS NULL THEN 'No Score'
        WHEN score >= 90 THEN 'Excellent'
        WHEN score >= 50 THEN 'Pass'
        ELSE 'Fail'
    END AS performance_category
FROM students;

SQL kindly sorts the students like a strict examiner: 90 and up is "Excellent", 50 and up is "Pass". If you don’t have enough points... well, you get the idea.

Grouping and Handling NULL

Working with groups of data is another area where CASE WHEN really shines. Imagine we have an orders table, and we want to count the total number of orders by status, including orders with a NULL status.

SELECT
    CASE
        WHEN status IS NULL THEN 'No Status'
        ELSE status
    END AS order_status,
    COUNT(*)
FROM orders
GROUP BY 
    CASE
        WHEN status IS NULL THEN 'No Status'
        ELSE status
    END;

This query neatly handles empty NULL statuses and swaps in 'No Status' instead, then counts the total orders in each group. Here’s the result:

Practical Cases: "Magic Tricks" with CASE WHEN

Example 1: Sorting with NULL Handling

Sometimes you want NULL values to show up either first or last in a sorted list. This is common, for example, in task lists where high-priority tasks should be at the top, and tasks with no priority (NULL) should be at the bottom.

SELECT
    task_name,
    priority,
    CASE
        WHEN priority IS NULL THEN 1
        ELSE 0
    END AS priority_sort
FROM tasks
ORDER BY priority_sort ASC, priority ASC;

Here we added a "virtual" column priority_sort that puts NULL values at the bottom, and sorts the rest in ascending order.

Example 2: Calculations with NULL Handling

Now imagine we’re calculating the final order total in the orders table, where the discount column might be NULL if there’s no discount.

We need to swap NULL for 0 so the calculation doesn’t break.

SELECT 
    order_id,
    total_price,
    discount,
    total_price - 
    CASE
        WHEN discount IS NULL THEN 0
        ELSE discount
    END AS final_price
FROM orders;

This magic trick with CASE makes sure NULL doesn’t mess up your math.

For order_id = 101: discount = 10, final_price = 100 − 10 = 90. For order_id = 102: discount = NULL, CASE returns 0, final_price = 200 − 0 = 200. For order_id = 103: discount = 15, final_price = 150 − 15 = 135. For order_id = 104: discount = NULL, CASE returns 0, final_price = 120 − 0 = 120. For order_id = 105: discount = 5, final_price = 80 − 5 = 75.

Example 3: Showing User Statuses

In everyday work, you’ll often need to show a user’s status (like "Active" or "Pending") or point out missing data. For example, in the users table, there’s a last_login column with the date of the last login.

SELECT
    user_id,
    name,
    CASE
        WHEN last_login IS NULL THEN 'Never Logged In'
        WHEN last_login < CURRENT_DATE - INTERVAL '30 days' THEN 'Inactive'
        ELSE 'Active'
    END AS user_status
FROM users;

With this query, the admin system comes alive: users who never logged in are called "Never Logged In", and those who haven’t logged in for a while are "Inactive". The rest are active!

Common Mistakes and How to Avoid Them

Forgetting ELSE: If you don’t add ELSE, SQL will just return NULL if none of the conditions match. That’s not always what you want. So it’s better to always specify ELSE, even if you think you’ve covered all the cases.

CASE
    WHEN condition THEN result
    -- ELSE 'Default value' -- don’t forget!
END

Complex conditions without parentheses: If you have several complex conditions with AND, OR, or NOT, always use parentheses. Without them, your query might start "thinking" wrong.

CASE
    WHEN (column1 IS NOT NULL AND column2 > 5) THEN 'Valid'
    ELSE 'Invalid'
END

Working with NULL: Remember, NULL is never equal (=) to anything. For example:

CASE
    WHEN column = NULL THEN 'Nope!' -- Wrong!
    WHEN column IS NULL THEN 'Correct!' -- This is right.
END