Snowflake
Apply "If-Then" Logic

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:


_10
CASE
_10
WHEN <condition1> THEN <result1>
_10
[ WHEN <condition2> THEN <result2> ]
_10
[ ... ]
_10
[ ELSE <result3> ]
_10
END

Expression-based CASE

This form evaluates an expression against several possible values, returning a result for the first match:


_10
CASE <expr>
_10
WHEN <value1> THEN <result1>
_10
[ WHEN <value2> THEN <result2> ]
_10
[ ... ]
_10
[ ELSE <result3> ]
_10
END

Common use cases

  • Customer segmentation: Businesses often categorize their customers based on spending to target marketing efforts:

_10
SELECT
_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
_10
FROM
_10
sales;

  • Event response: Event management systems might use CASE WHEN to trigger actions based on event types:

_10
SELECT
_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
_10
FROM
_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
_10
SELECT
_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

idcharacterlocationhitchhikes
1Arthur DentEarth0
2Ford PrefectBetelgeuse Five2
3Zaphod BeeblebroxMagrathea3
4TrillianEarth1
5MarvinMagrathea1

Snowflake SQL Query


_11
SELECT
_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
_11
FROM
_11
character_info;

Output

idcharacterlocationhitchhikessafety_rating
1Arthur DentEarth0mostly harmless
2Ford PrefectBetelgeuse Five2unknown
3Zaphod BeeblebroxMagrathea3extremely dangerous
4TrillianEarth1mostly harmless
5MarvinMagrathea1extremely 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

idcharacterlocationhitchhikes
1Arthur DentEarth0
2Ford PrefectBetelgeuse Five2
3Zaphod BeeblebroxMagrathea3
4TrillianEarth1
5MarvinMagrathea1

Snowflake SQL Query


_13
SELECT
_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
_13
FROM
_13
character_info;

Output

idcharacterlocationhitchhikesmood
1Arthur DentEarth0bored
2Ford PrefectBetelgeuse Five2excited
3Zaphod BeeblebroxMagrathea3veteran hitchhiker
4TrillianEarth1curious
5MarvinMagrathea1curious
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free