How to Use CASE WHEN in Redshift

Galaxy Glossary

How do I use CASE WHEN in Amazon Redshift?

CASE WHEN lets you create conditional logic inside SELECT, UPDATE, and ORDER BY statements in Amazon Redshift.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does CASE WHEN do in Redshift?

CASE WHEN evaluates conditions in order and returns the first matching result, allowing if-then-else logic directly in SQL queries.

How do I write a simple CASE expression?

Start with CASE, list each WHEN condition THEN result, add an optional ELSE default, and finish with END. Embed it in SELECT, UPDATE, or ORDER BY clauses.

Which syntax forms exist?

Redshift supports searched CASE (multiple Boolean tests) and simple CASE (compare one expression to many values). Both can appear anywhere an expression is allowed.

Where is CASE WHEN useful in ecommerce?

Calculate customer tiers, flag low stock products, or group order amounts into ranges—all without extra joins or subqueries.

What are best practices?

Order WHEN clauses from most to least likely, keep logic short, and always include an ELSE to avoid NULL surprises.

How can I nest CASE statements?

Redshift permits nesting, but limit depth to two levels for readability. Use CTEs if logic grows complex.

Does CASE impact performance?

CASE runs per row; avoid heavy scalar functions inside WHEN clauses. Push filters to WHERE when possible.

Why How to Use CASE WHEN in Redshift is important

How to Use CASE WHEN in Redshift Example Usage


-- Flag VIP customers based on lifetime spend
WITH customer_totals AS (
    SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_spend
    FROM Customers c
    JOIN Orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name
)
SELECT id,
       name,
       CASE
           WHEN lifetime_spend >= 10000 THEN 'Platinum'
           WHEN lifetime_spend >= 5000  THEN 'Gold'
           WHEN lifetime_spend >= 1000  THEN 'Silver'
           ELSE 'Bronze'
       END AS tier
FROM customer_totals
ORDER BY lifetime_spend DESC;

How to Use CASE WHEN in Redshift Syntax


-- Searched CASE (general)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [...]
    ELSE default_result
END

-- Simple CASE (compare one expression)
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    [...]
    ELSE default_result
END

-- Example in SELECT (ecommerce)
SELECT
    o.id,
    CASE
        WHEN o.total_amount >= 500 THEN 'High-Value'
        WHEN o.total_amount >= 100 THEN 'Medium-Value'
        ELSE 'Low-Value'
    END AS order_segment
FROM Orders o;

-- Example in UPDATE
UPDATE Products
SET stock_status = CASE
        WHEN stock = 0 THEN 'out_of_stock'
        WHEN stock < 10 THEN 'low_stock'
        ELSE 'in_stock'
    END;

Common Mistakes

Placing non-deterministic functions (e.g., RANDOM()) inside WHEN clauses. This forces evaluation for every row and prevents result caching. Move such logic outside or use a subquery.
Omitting an ELSE clause. When no conditions match, Redshift returns NULL, which can break downstream analytics. Always include a safe default value.

Frequently Asked Questions (FAQs)

Can I use CASE WHEN in a WHERE clause?

No. Use CASE in SELECT, ORDER BY, or UPDATE. For conditional filtering, combine Boolean logic directly in WHERE.

Is CASE WHEN the same as IF in other languages?

Conceptually yes, but CASE is set-based and evaluates per row instead of per statement.

Does Redshift support IIF or DECODE?

No. Stick with ANSI-standard CASE WHEN for maximum portability.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie