Snowflake CASE WHEN: How to Apply "if-then" Logic
The CASE WHEN statement in Snowflake works like a chain of "if-then" statements. It checks a list of conditions sequentially and returns a result for the first true condition. If no conditions are true, it can return a specified default result using an ELSE, or it will return NULL if ELSE is not provided.
What it does
Imagine CASE WHEN as a decision-making tool in your query, similar to choosing paths in a flowchart, where each decision leads to a different outcome.
Syntax
Condition-based CASE
This form checks each condition one after the other:
_10CASE_10 WHEN <condition1> THEN <result1>_10 [ WHEN <condition2> THEN <result2> ]_10 [ ... ]_10 [ ELSE <result3> ]_10END
Expression-based CASE
This form evaluates an expression against several possible values, returning a result for the first match:
_10CASE <expr>_10 WHEN <value1> THEN <result1>_10 [ WHEN <value2> THEN <result2> ]_10 [ ... ]_10 [ ELSE <result3> ]_10END
Common use cases
- Customer segmentation: Businesses often categorize their customers based on spending to target marketing efforts:
_10SELECT_10 customer_id,_10 CASE_10 WHEN total_spent > 1000 THEN 'High spender'_10 WHEN total_spent > 500 THEN 'Medium spender'_10 ELSE 'Low spender'_10 END AS customer_type_10FROM_10 sales;
- Event response: Event management systems might use CASE WHEN to trigger actions based on event types:
_10SELECT_10 event_id,_10 CASE event_type_10 WHEN 'Error' THEN 'Send alert to admin'_10 WHEN 'Warning' THEN 'Log warning'_10 ELSE 'Ignore'_10 END AS action_taken_10FROM_10 system_events;
- Time-based greeting: Websites can display a dynamic greeting based on the user's current time:
_10-- Assuming 'current_hour' is the hour in 24-hour format_10SELECT_10 CASE_10 WHEN hour (current_timestamp) < 12 THEN 'Good Morning!'_10 WHEN hour (current_timestamp) < 18 THEN 'Good Afternoon!'_10 ELSE 'Good Evening!'_10 END AS greeting;
Examples
Condition-based CASE
In this example, we imagine a scenario where characters from "The Hitchhiker's Guide to the Galaxy" react to different situations based on their location.
Input
Table: character_info
| id | character | location | hitchhikes |
|---|---|---|---|
| 1 | Arthur Dent | Earth | 0 |
| 2 | Ford Prefect | Betelgeuse Five | 2 |
| 3 | Zaphod Beeblebrox | Magrathea | 3 |
| 4 | Trillian | Earth | 1 |
| 5 | Marvin | Magrathea | 1 |
Snowflake SQL Query
_11SELECT_11 id,_11 character,_11 location,_11 hitchhikes CASE_11 WHEN location = 'Earth' THEN 'mostly harmless'_11 WHEN location = 'Magrathea' THEN 'extremely dangerous'_11 ELSE 'unknown'_11 END AS safety_rating_11FROM_11 character_info;
Output
| id | character | location | hitchhikes | safety_rating |
|---|---|---|---|---|
| 1 | Arthur Dent | Earth | 0 | mostly harmless |
| 2 | Ford Prefect | Betelgeuse Five | 2 | unknown |
| 3 | Zaphod Beeblebrox | Magrathea | 3 | extremely dangerous |
| 4 | Trillian | Earth | 1 | mostly harmless |
| 5 | Marvin | Magrathea | 1 | extremely dangerous |
Expression-based CASE
Here, let's use the CASE statement to determine the characters' moods based on how many times they've hitchhiked.
Input
Table: character_info
| id | character | location | hitchhikes |
|---|---|---|---|
| 1 | Arthur Dent | Earth | 0 |
| 2 | Ford Prefect | Betelgeuse Five | 2 |
| 3 | Zaphod Beeblebrox | Magrathea | 3 |
| 4 | Trillian | Earth | 1 |
| 5 | Marvin | Magrathea | 1 |
Snowflake SQL Query
_13SELECT_13 id,_13 character,_13 location,_13 hitchhikes,_13 CASE hitchhikes_13 WHEN 0 THEN 'bored'_13 WHEN 1 THEN 'curious'_13 WHEN 2 THEN 'excited'_13 ELSE 'veteran hitchhiker'_13 END AS mood_13FROM_13 character_info;
Output
| id | character | location | hitchhikes | mood |
|---|---|---|---|---|
| 1 | Arthur Dent | Earth | 0 | bored |
| 2 | Ford Prefect | Betelgeuse Five | 2 | excited |
| 3 | Zaphod Beeblebrox | Magrathea | 3 | veteran hitchhiker |
| 4 | Trillian | Earth | 1 | curious |
| 5 | Marvin | Magrathea | 1 | curious |
