Paradime | CASE

SQL Keywords

CASE

Feb 23, 2026

min read

Category: Conditional Expressions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

CASE provides if-then-else conditional logic in SQL, allowing you to return different values based on conditions. It's essential for data transformation, categorization, and implementing business logic within queries.

Syntax

Simple CASE (equality checks):

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Searched CASE (complex conditions):

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

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

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

Platform-Specific Notes

Snowflake:

Fully supports both simple and searched CASE
Also has IFF(condition, true_value, false_value) shorthand
Evaluates conditions sequentially (first match wins)

BigQuery:

Fully supports both CASE types
Also has IF(condition, true_value, false_value) function
Case-insensitive string comparisons by default

Databricks:

Standard SQL CASE implementation
Also supports Spark SQL IF function
Compatible with Hive syntax

Example 1: Categorizing Values

All Platforms:

SELECT 
    product_name,
    price,
    CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 200 THEN 'Mid-Range'
        WHEN price BETWEEN 200 AND 500 THEN 'Premium'
        ELSE 'Luxury'
    END as price_category,
    CASE
        WHEN price < 50 THEN 0.05
        WHEN price BETWEEN 50 AND 200 THEN 0.10
        ELSE 0.15
    END as discount_rate
FROM

SELECT 
    product_name,
    price,
    CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 200 THEN 'Mid-Range'
        WHEN price BETWEEN 200 AND 500 THEN 'Premium'
        ELSE 'Luxury'
    END as price_category,
    CASE
        WHEN price < 50 THEN 0.05
        WHEN price BETWEEN 50 AND 200 THEN 0.10
        ELSE 0.15
    END as discount_rate
FROM

SELECT 
    product_name,
    price,
    CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 200 THEN 'Mid-Range'
        WHEN price BETWEEN 200 AND 500 THEN 'Premium'
        ELSE 'Luxury'
    END as price_category,
    CASE
        WHEN price < 50 THEN 0.05
        WHEN price BETWEEN 50 AND 200 THEN 0.10
        ELSE 0.15
    END as discount_rate
FROM

Sample Data (products table):

product_name	price
Mouse	25.99
Keyboard	89.99
Monitor	349.99
Laptop	1299.99
USB Cable	12.99

Result:

product_name	price	price_category	discount_rate
Mouse	25.99	Budget	0.05
Keyboard	89.99	Mid-Range	0.10
Monitor	349.99	Premium	0.15
Laptop	1299.99	Luxury	0.15
USB Cable	12.99	Budget	0.05

Example 2: Simple CASE with Status Codes

All Platforms:

SELECT 
    order_id,
    customer_name,
    status_code,
    CASE status_code
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Processing'
        WHEN 3 THEN 'Shipped'
        WHEN 4 THEN 'Delivered'
        WHEN 5 THEN 'Cancelled'
        ELSE 'Unknown'
    END as status_text,
    CASE status_code
        WHEN 4 THEN 'Complete'
        WHEN 5 THEN 'Complete'
        ELSE 'In Progress'
    END as status_group
FROM

SELECT 
    order_id,
    customer_name,
    status_code,
    CASE status_code
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Processing'
        WHEN 3 THEN 'Shipped'
        WHEN 4 THEN 'Delivered'
        WHEN 5 THEN 'Cancelled'
        ELSE 'Unknown'
    END as status_text,
    CASE status_code
        WHEN 4 THEN 'Complete'
        WHEN 5 THEN 'Complete'
        ELSE 'In Progress'
    END as status_group
FROM

SELECT 
    order_id,
    customer_name,
    status_code,
    CASE status_code
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Processing'
        WHEN 3 THEN 'Shipped'
        WHEN 4 THEN 'Delivered'
        WHEN 5 THEN 'Cancelled'
        ELSE 'Unknown'
    END as status_text,
    CASE status_code
        WHEN 4 THEN 'Complete'
        WHEN 5 THEN 'Complete'
        ELSE 'In Progress'
    END as status_group
FROM

Sample Data (orders table):

order_id	customer_name	status_code
101	John Smith	1
102	Maria Garcia	3
103	Sarah Johnson	4
104	Ahmed Hassan	2
105	Lisa Wang	5

Result:

order_id	customer_name	status_code	status_text	status_group
101	John Smith	1	Pending	In Progress
102	Maria Garcia	3	Shipped	In Progress
103	Sarah Johnson	4	Delivered	Complete
104	Ahmed Hassan	2	Processing	In Progress
105	Lisa Wang	5	Cancelled	Complete

Example 3: CASE in Aggregation

All Platforms:

SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(CASE WHEN salary >= 80000 THEN 1 END) as high_earners,
    COUNT(CASE WHEN salary < 80000 THEN 1 END) as regular_earners,
    SUM(CASE 
        WHEN performance_rating = 'Excellent' THEN salary * 0.10
        WHEN performance_rating = 'Good' THEN salary * 0.05
        ELSE 0
    END) as total_bonus_amount
FROM employees
GROUP BY

SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(CASE WHEN salary >= 80000 THEN 1 END) as high_earners,
    COUNT(CASE WHEN salary < 80000 THEN 1 END) as regular_earners,
    SUM(CASE 
        WHEN performance_rating = 'Excellent' THEN salary * 0.10
        WHEN performance_rating = 'Good' THEN salary * 0.05
        ELSE 0
    END) as total_bonus_amount
FROM employees
GROUP BY

SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(CASE WHEN salary >= 80000 THEN 1 END) as high_earners,
    COUNT(CASE WHEN salary < 80000 THEN 1 END) as regular_earners,
    SUM(CASE 
        WHEN performance_rating = 'Excellent' THEN salary * 0.10
        WHEN performance_rating = 'Good' THEN salary * 0.05
        ELSE 0
    END) as total_bonus_amount
FROM employees
GROUP BY

Sample Data (employees table):

department	employee_name	salary	performance_rating
Sales	Alice	75000	Excellent
Sales	Bob	65000	Good
Sales	Carol	85000	Excellent
IT	Diana	90000	Excellent
IT	Eric	95000	Good
IT	Frank	70000	Average
Marketing	Grace	72000	Good

Result:

department	total_employees	high_earners	regular_earners	total_bonus_amount
IT	3	2	1	13750
Marketing	1	0	1	3600
Sales	3	1	2	10750

Example 4: Nested CASE Statements

All Platforms:

SELECT 
    customer_id,
    order_total,
    customer_type,
    CASE customer_type
        WHEN 'Premium' THEN 
            CASE 
                WHEN order_total > 1000 THEN order_total * 0.20
                WHEN order_total > 500 THEN order_total * 0.15
                ELSE order_total * 0.10
            END
        WHEN 'Standard' THEN
            CASE
                WHEN order_total > 500 THEN order_total * 0.10
                ELSE order_total * 0.05
            END
        ELSE order_total * 0.02
    END as discount_amount
FROM

SELECT 
    customer_id,
    order_total,
    customer_type,
    CASE customer_type
        WHEN 'Premium' THEN 
            CASE 
                WHEN order_total > 1000 THEN order_total * 0.20
                WHEN order_total > 500 THEN order_total * 0.15
                ELSE order_total * 0.10
            END
        WHEN 'Standard' THEN
            CASE
                WHEN order_total > 500 THEN order_total * 0.10
                ELSE order_total * 0.05
            END
        ELSE order_total * 0.02
    END as discount_amount
FROM

SELECT 
    customer_id,
    order_total,
    customer_type,
    CASE customer_type
        WHEN 'Premium' THEN 
            CASE 
                WHEN order_total > 1000 THEN order_total * 0.20
                WHEN order_total > 500 THEN order_total * 0.15
                ELSE order_total * 0.10
            END
        WHEN 'Standard' THEN
            CASE
                WHEN order_total > 500 THEN order_total * 0.10
                ELSE order_total * 0.05
            END
        ELSE order_total * 0.02
    END as discount_amount
FROM

Sample Data:

customer_id	order_total	customer_type
101	1500	Premium
102	600	Premium
103	800	Standard
104	300	Standard
105	500	Guest

Result:

customer_id	order_total	customer_type	discount_amount
101	1500	Premium	300.00
102	600	Premium	90.00
103	800	Standard	80.00
104	300	Standard	15.00
105	500	Guest	10.00

Platform-Specific Shortcuts

Snowflake IFF:

SELECT 
    product_name,
    IFF(in_stock, 'Available', 'Out of Stock') as availability
FROM

SELECT 
    product_name,
    IFF(in_stock, 'Available', 'Out of Stock') as availability
FROM

SELECT 
    product_name,
    IFF(in_stock, 'Available', 'Out of Stock') as availability
FROM

BigQuery IF:

SELECT 
    product_name,
    IF(in_stock, 'Available', 'Out of Stock') as availability
FROM

SELECT 
    product_name,
    IF(in_stock, 'Available', 'Out of Stock') as availability
FROM

SELECT 
    product_name,
    IF(in_stock, 'Available', 'Out of Stock') as availability
FROM

Common Use Cases

Data categorization: Group continuous values into buckets
Conditional aggregation: Count/sum based on conditions
Status translation: Convert codes to human-readable text
Null handling: Provide default values for NULL
Business rules: Implement pricing tiers, discount logic
Data cleaning: Standardize inconsistent values

Best Practices

Order matters: Conditions are evaluated sequentially - first match wins
Always include ELSE: Prevent unexpected NULLs
Use searched CASE for complex logic: More flexible than simple CASE
Consider performance: Multiple CASE in same query can be costly
Extract to views/CTEs: Complex CASE logic may be reusable
NULL handling: Remember NULL doesn't equal anything, including NULL

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources