Case Statements With Multiple Conditions In Snowflake (Examples)

by

A CASE statement lets you perform conditional logic in SQL. It’s like an if-then-else structure found in other programming languages.

A CASE statement with multiple conditions evaluates more than one condition in its structure.

This article is a practical walkthrough of using CASE statements with multiple conditions in Snowflake. It also covers nested CASE statements.

As well as practical examples, you’ll learn about common errors and how to deal with them.

Case Statement With Multiple Conditions

Let’s have a quick refresher of what a CASE statement looks like with one condition.

CASE
    WHEN condition1 THEN result1
    ELSE default_result
END

You add more conditions by additional WHEN/THEN sections. Snowflake evaluates these sequentially in the order that you write them.

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

Practical Example

Let’s say you have a product table where each product has a price and a category.

You want conditions that group the products along these lines:

  • Budget Groceries
  • Budget Electronics
  • Premium Groceries
  • Premium Electronics

Here’s how you can use a CASE statement with multiple conditions:

SELECT name, price, category,
CASE 
    WHEN price < 10 AND category = “Groceries” THEN 'Budget Groceries '
    WHEN price >= 10 AND category = “Groceries” THEN 'Premium Groceries '
    WHEN price < 10 AND category = “Electronics” THEN 'Budget Electronics '
    WHEN price >= 10 AND category = “Electronics” THEN 'Premium Electronics '
    ELSE 'Uncategorized'
END AS price_category
FROM product;

Always remember that the conditions are evaluated in order, and the CASE statement will return the result from the first condition that is true.

So, the order of conditions in your CASE statement can significantly impact your results.

5 Key Factors To Consider With Multiple Conditions

You need to keep some key factors in mind when writing CASE statements with multiple conditions. Here are our top five to be aware of:

  1. Order of Evaluation
  2. Coverage
  3. Complexity
  4. Performance
  5. Handling Nulls

Order of Evaluation Of Conditions

The conditions in a CASE statement are evaluated in the order they appear, from top to bottom.

Once a condition is met, the CASE statement will return the corresponding result and ignore the rest of the conditions. Therefore, you should order your conditions carefully to avoid unintended results.

For efficiency, you will often want to put the most likely condition first. That means you save processing time and resources on evaluating the next ones.

Coverage of Conditions

You should ensure that your conditions collectively cover all possible scenarios you’re interested in.

Any rows that don’t meet any condition will fall into the ELSE category (as long as you specified one).

When you’re testing with realistic data, check for ELSE clauses that frequently trigger. This may mean that your conditions are not comprehensive enough.

Complexity of Conditions

Overly complicated conditions can make your code harder to read and maintain.

If you’re dealing with very complex logic, consider breaking it down into multiple steps. You should also use comments to explain your reasoning.

Performance Impact

Each condition in a CASE statement adds to the computational work that Snowflake needs to perform.

While this is not usually a problem for small datasets, it can slow down your queries on larger datasets. Keep an eye on performance and look for optimization opportunities.

NULL Handling

NULL represents missing or unknown data. If the field you’re working with contains NULL values, you need to decide how to handle them in your conditions.

The default behavior is that NULL values in any condition of a CASE statement will yield NULL as the result. If that’s not what you want, be sure to have a condition that handles them explicitly.

4 Common Errors

I’ve experienced my fair (or unfair) share of errors when writing complex CASE statements.

Here are my most common four types of errors, and my best tips on how to avoid them.

Incorrect Operators

In the examples, I used a condition on the monetary value of price. It’s very easy when using greater than/less than operators to get the boundaries wrong.

For example, suppose these are your conditions:

WHEN price > 10 THEN …
WHEN price < 10 THEN …

If those are the only two conditions, then you haven’t specified what should happen when the price is exactly $10.

Misordered Conditions

Because CASE statements evaluate conditions in the order they are listed, it’s possible to end up with inaccurate results if the conditions are not correctly ordered.

If you put a very broad condition before more specific conditions, it can capture cases intended for the latter. The trick here is to put your specific conditions first.

Missing ELSE Clause

While the ELSE clause is technically optional, excluding it can lead to NULL results when none of the conditions in the CASE statement are met.

If that’s not what you want, always include an ELSE clause to handle any unexpected cases.

Data Type Mismatches

The data types of the results in each THEN clause should ideally be the same or compatible.

Incompatible data types can result in errors or unexpected results.

How To Troubleshoot Errors

Troubleshooting errors with these complex statements can be challenging.

Here are my best tips for dealing with unexpected results.

Simplify the query

Try simplifying your query by reducing the number of conditions or dividing the logic into smaller parts.

Test these smaller statements and gradually add complexity.


Check Your Data

The error may not be with your CASE conditions. The fault may lie within the data itself.

Check your data for missing values, incorrect data types, or unexpected values that might be causing issues.

Review the Logical Flow

Get away from your SQL editor and make a diagram of the steps and logic you are trying to implement.

Pay particular attention to the order of conditions and watch for ones that are unintentionally overriding others.

Nested Case Statements

In some situations, a multiple-condition CASE statement may not be enough to handle the complexity of the analytical requirements.

You may need to switch to using nested CASE statements.

A nested CASE statement is essentially a CASE statement within a CASE statement. This structure can provide a solution when logic needs to be applied based on the results of previous conditions.

You can chain conditions to gain more granular control over how you process your data. You can of course use multiple conditions as part of the nested case statements.

This is the syntax:

CASE
    WHEN condition1 THEN 
        CASE 
            WHEN sub_condition1 THEN sub_result1
            ELSE sub_default_result
        END
    ELSE default_result
END

Bear in mind that nested statements add complexity and can be difficult to read.

Example of a Nested Case Statement with Multiple Conditions

I’ll use the same product table as in the previous example.

Suppose you want to apply different discount rates to each product based on both its category and its price. Here’s how a nested CASE statement can do this:

SELECT ID, name, price, category,
CASE 
    WHEN category = 'Electronics' THEN 
        CASE 
            WHEN price >= 1000 THEN price * 0.85  --  Apply a 15% discount
            WHEN price >= 500 AND price < 1000 THEN price * 0.9  -- Apply a 10% discount
            ELSE price * 0.95  -- Apply a 5% discount
        END
    WHEN category = 'Groceries' THEN 
        CASE 
            WHEN price >= 100 THEN price * 0.9  -- Apply a 10% discount
            ELSE price  -- No discount
        END
    ELSE price  -- No discount for other categories
END AS discounted_price
FROM product;

In this query, the outer CASE statement checks the category of each product. For each category, a different nested CASE statement is evaluated, which applies a discount based on the price of the product.

Notice how I’ve kept the logic as clear as possible, despite the complexity of the nested CASE statement. The readability of the code is crucial when dealing with nested conditions, as it can quickly become complicated.