How to use CASE WHEN in SQL Server

How do you use CASE WHEN for conditional logic in SQL Server?

CASE WHEN performs conditional logic, returning different values based on evaluated expressions within a query.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does CASE WHEN solve?

CASE WHEN lets you embed IF-ELSE logic inside SQL statements, replacing nested queries or client-side code. It produces calculated columns, categorizes data, and drives conditional aggregations.

How is the basic CASE WHEN syntax structured?

Write CASE, list WHEN condition THEN result pairs, add an optional ELSE fallback, and close with END. The expression returns the result whose condition evaluates to TRUE.

How do I apply multiple conditions?

Chain additional WHEN clauses. SQL Server evaluates them top-down and stops at the first TRUE match, so order matters. Place the most specific conditions first.

How can I categorize customer spend levels?

Select Orders.total_amount and wrap it in CASE to label each order as 'High', 'Medium', or 'Low'. This provides readable buckets without altering stored data.

How do I use CASE inside ORDER BY?

Return numeric values in CASE so you can sort rows by custom priority. This is handy for pushing flagged or VIP records to the top of result sets.

What is a Searched CASE vs. Simple CASE?

Searched CASE compares full boolean conditions (WHEN total_amount > 500). Simple CASE compares a single expression to multiple literals (CASE status WHEN 'open' THEN …).

Why place CASE in WHERE is discouraged?

Using CASE in WHERE often leads to scans because each row must evaluate every branch. Instead, split the query with UNION ALL or use separate predicates.

Best practices for performance?

Keep CASE determinate and non-volatile, avoid scalar functions inside, and align data types across THEN results. Always include ELSE to prevent NULL surprises.

Why How to use CASE WHEN in SQL Server is important

How to use CASE WHEN in SQL Server Example Usage


-- Highlight VIP customers who placed high-value orders in 2024
SELECT
    c.id,
    c.name,
    SUM(o.total_amount) AS yearly_spend,
    CASE
        WHEN SUM(o.total_amount) >= 10000 THEN 'Platinum'
        WHEN SUM(o.total_amount) >= 5000  THEN 'Gold'
        ELSE 'Silver'
    END AS tier
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
WHERE YEAR(o.order_date) = 2024
GROUP BY c.id, c.name
ORDER BY tier DESC, yearly_spend DESC;

How to use CASE WHEN in SQL Server Syntax


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

-- e-commerce searched CASE example
SELECT
    o.id,
    o.total_amount,
    CASE
        WHEN o.total_amount >= 500 THEN 'High'
        WHEN o.total_amount >= 200 THEN 'Medium'
        ELSE 'Low'
    END AS spend_level
FROM Orders AS o;

Common Mistakes

Mixing data types in THEN results causes implicit conversions and poor indexing. Cast all THEN values to a common type to maintain plan stability.
Placing CASE in the WHERE clause makes every predicate non-SARGable, forcing table scans. Split logic into separate queries or use OR conditions instead.