How to use CASE WHEN in Snowflake

How do I use CASE WHEN in Snowflake?

CASE WHEN lets you perform conditional logic inside a SELECT statement, returning different values based on Boolean tests.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CASE WHEN do in Snowflake?

CASE WHEN evaluates Boolean conditions in order and returns the first matching result; if none match, it returns the ELSE value or NULL.

When should I prefer CASE over IF?

Use CASE WHEN for inline column-level decisions inside SELECT, UPDATE, and ORDER BY; use IF only in stored procedures or Snowflake Scripting blocks.

How is the basic syntax structured?

Start with CASE, add multiple WHEN condition THEN result pairs, finish with optional ELSE and mandatory END.

Can I nest CASE statements?

Yes.Wrap an inner CASE inside THEN to cascade logic, but keep nesting shallow for readability.

Example: flag high-value orders

This query labels orders over $500 as ‘high’, between $100–$500 as ‘medium’, and the rest as ‘low’.

SELECT id, total_amount, CASE WHEN total_amount > 500 THEN 'high' WHEN total_amount BETWEEN 100 AND 500 THEN 'medium' ELSE 'low' END AS order_size FROM Orders;

Best practice: order conditions by specificity

Place the most restrictive condition first; CASE stops searching after the first match, improving clarity and performance.

Why How to use CASE WHEN in Snowflake is important

How to use CASE WHEN in Snowflake Example Usage


-- Apply discount only to low-stock, high-price products
SELECT p.id,
       p.name,
       p.price,
       CASE WHEN p.stock < 10 AND p.price > 100 THEN p.price * 0.9
            ELSE p.price END AS final_price
FROM Products p;

How to use CASE WHEN in Snowflake Syntax


CASE [column_or_expression]
    WHEN <expression> THEN <result>
    [...]
    ELSE <default_result>
END

-- Searched form (most common)
CASE
    WHEN <condition1> THEN <result1>
    WHEN <condition2> THEN <result2>
    [...]
    ELSE <default_result>
END

-- Ecommerce example: tier customers by spend
SELECT c.id,
       CASE WHEN SUM(o.total_amount) > 5000 THEN 'Platinum'
            WHEN SUM(o.total_amount) > 2000 THEN 'Gold'
            ELSE 'Standard' END AS tier
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id;

Common Mistakes

Omitting END; Snowflake throws "syntax error line ... at end of input". Fix by always adding END to close the CASE block.
Placing a broad condition first; later specific conditions never run. Fix by ordering WHEN clauses from most specific to least specific.