powershelldba.de · Uwe Janke

The SQL CASE Expression: Syntax, Multiple Conditions, Categories, and Alternatives

CASE is the one piece of conditional logic every SQL query writer ends up using. Here is how it works, how to combine conditions without bucketing rows wrongly, how to build categories and pivots with it, the traps that cause real errors, and when IIF, CHOOSE, COALESCE, GREATEST or a plain lookup table is the better tool.

What Is CASE in SQL?

CASE returns one value out of several, depending on conditions you define. Strictly speaking it is an expression, not a statement: it does not control which code runs (that is IF ... ELSE in T-SQL), it simply produces a value. That distinction matters, because it means CASE can go anywhere a value can go:

All examples below run against this small table (tested on SQL Server 2025, but everything except GREATEST/LEAST and IS DISTINCT FROM works on any supported version):

CREATE TABLE #Orders (
    OrderId      int PRIMARY KEY,
    CustomerName varchar(30),
    OrderDate    date,
    Amount       decimal(10,2),
    Status       char(1),      -- N = new, S = shipped, D = delivered, C = cancelled
    Country      char(2),
    ShippedDate  date NULL
);

INSERT #Orders VALUES
 (1, 'Contoso',   '2026-01-14',   45.00, 'N',  'DE', NULL),
 (2, 'Fabrikam',  '2026-02-03',  380.00, 'S',  'US', '2026-02-05'),
 (3, 'Northwind', '2026-04-22', 1250.00, 'D',  'DE', '2026-04-23'),
 (4, 'Contoso',   '2026-05-09',   99.99, 'C',  'AT', NULL),
 (5, 'Tailspin',  '2026-07-30', 5400.00, 'S',  'US', '2026-08-02'),
 (6, 'Fabrikam',  '2026-10-01',    0.00, NULL, 'DE', NULL);

CASE WHEN THEN ELSE: The Syntax

CASE comes in two forms.

Simple CASE: compare one expression against values

SELECT OrderId,
       CASE Status
            WHEN 'N' THEN 'New'
            WHEN 'S' THEN 'Shipped'
            WHEN 'D' THEN 'Delivered'
            WHEN 'C' THEN 'Cancelled'
            ELSE 'Unknown'
       END AS StatusText
FROM #Orders;

Simple CASE only tests equality. It is compact and easy to read when you are translating codes into text.

Searched CASE: any condition per branch

SELECT OrderId, Amount,
       CASE
            WHEN Amount >= 1000 THEN 'Large'
            WHEN Amount >= 100  THEN 'Medium'
            ELSE 'Small'
       END AS SizeBand
FROM #Orders;

Searched CASE takes a full Boolean condition in every WHEN: ranges, IS NULL, IN, LIKE, EXISTS, combinations with AND/OR. Anything a simple CASE can do, a searched CASE can do too.

Four rules worth memorizing

  1. The first matching WHEN wins. Branches are checked top to bottom, and evaluation stops at the first one that is true.
  2. No ELSE means ELSE NULL. A row that matches no branch gets NULL, silently.
  3. All branches return one data type. SQL Server picks the type with the highest precedence among all THEN and ELSE results and converts everything else to it (more on that trap below).
  4. Every CASE ends with END. Give the result a column alias, otherwise the column has no name.
Simple CASE never matches NULL. CASE Status WHEN NULL THEN ... compares Status = NULL, which is never true. Order 6 above has a NULL status and still lands in ELSE. Use the searched form: CASE WHEN Status IS NULL THEN 'No status' ... END.

How to Apply Multiple Conditions in CASE

Each WHEN in a searched CASE accepts a complete predicate, so multiple conditions are written exactly like in a WHERE clause:

SELECT OrderId,
       CASE
            WHEN Status = 'S'
                 AND ShippedDate IS NOT NULL
                 AND DATEDIFF(day, OrderDate, ShippedDate) > 2  THEN 'Shipped late'
            WHEN Status = 'S'                                    THEN 'Shipped on time'
            WHEN Status = 'N'
                 OR (Status IS NULL AND Amount = 0)              THEN 'Needs review'
            ELSE 'Closed'
       END AS Bucket
FROM #Orders;

Two habits keep multi-condition CASE expressions correct:

The ordering mistake is the most common CASE bug, and it does not raise an error. Compare these two bandings:

SELECT OrderId, Amount,
       CASE WHEN Amount > 100  THEN 'Medium'     -- catches 1250 and 5400 too
            WHEN Amount > 1000 THEN 'Large'      -- never reached
            ELSE 'Small' END AS Wrong,
       CASE WHEN Amount >= 1000 THEN 'Large'
            WHEN Amount >= 100  THEN 'Medium'
            ELSE 'Small' END AS Correct
FROM #Orders;
OrderId  Amount   Wrong   Correct
-------  -------  ------  -------
1          45.00  Small   Small
2         380.00  Medium  Medium
3        1250.00  Medium  Large
4          99.99  Small   Small
5        5400.00  Medium  Large
6           0.00  Small   Small

For range bands, also decide deliberately where the boundaries go (>= versus >) so a value of exactly 100 or exactly 1000 lands where the business expects it.

How to Create Categories with CASE

Categorizing rows (size bands, age groups, risk classes, fiscal periods) is the classic job for CASE. To report per category, you need to group by the CASE result. Repeating the whole expression in GROUP BY works, but it duplicates logic. A cleaner pattern is to compute it once with CROSS APPLY and reuse the name:

SELECT b.SizeBand,
       COUNT(*)      AS Orders,
       SUM(o.Amount) AS Revenue
FROM #Orders AS o
CROSS APPLY (VALUES (
    CASE WHEN o.Amount >= 1000 THEN 'Large'
         WHEN o.Amount >= 100  THEN 'Medium'
         ELSE 'Small' END
)) AS b(SizeBand)
GROUP BY b.SizeBand
ORDER BY MIN(o.Amount);
SizeBand  Orders  Revenue
--------  ------  -------
Small          3   144.99
Medium         1   380.00
Large          2  6650.00

A derived table or CTE does the same job. The point is to define each category exactly once, so the SELECT and the GROUP BY can never drift apart.

CASE in SELECT Queries (and Beyond)

Conditional aggregation: totals per condition in one pass

CASE inside an aggregate turns rows into columns without PIVOT. This is one of the most useful patterns in reporting:

SELECT Country,
       COUNT(*)                                           AS AllOrders,
       COUNT(CASE WHEN Status = 'S' THEN 1 END)           AS Shipped,
       SUM(CASE WHEN Status = 'C' THEN Amount ELSE 0 END) AS CancelledValue,
       SUM(CASE WHEN MONTH(OrderDate) <= 6 THEN Amount ELSE 0 END) AS H1,
       SUM(CASE WHEN MONTH(OrderDate) >= 7 THEN Amount ELSE 0 END) AS H2
FROM #Orders
GROUP BY Country;
Country  AllOrders  Shipped  CancelledValue  H1       H2
-------  ---------  -------  --------------  -------  -------
AT               1        0           99.99    99.99     0.00
DE               3        0            0.00  1295.00     0.00
US               2        2            0.00   380.00  5400.00

COUNT(CASE WHEN ... THEN 1 END) works because the missing ELSE yields NULL, and COUNT ignores NULLs. For SUM, use ELSE 0 if you want 0 instead of NULL for groups with no matching rows.

Compared with PIVOT, conditional aggregation is easier to read, handles several different aggregates in one query, and can pivot on conditions rather than only on exact values.

Custom sort order

SELECT OrderId, Status
FROM #Orders
ORDER BY CASE Status WHEN 'N' THEN 1   -- open work first
                     WHEN 'S' THEN 2
                     WHEN 'D' THEN 3
                     ELSE 4 END,
         OrderDate;

Different values per row in an UPDATE

UPDATE #Orders
SET    Status = CASE WHEN ShippedDate < '2026-06-01' THEN 'D' ELSE Status END
WHERE  Status = 'S';

Note the ELSE Status: without it, every row that matches the WHERE clause but not the WHEN would be set to NULL.

CASE in WHERE: possible, but usually not the best idea

CASE in a WHERE clause is legal, but wrapping a column in an expression generally prevents an index seek on that column. Plain Boolean logic is almost always clearer and lets the optimizer use indexes:

-- Works, but hides the column inside an expression
WHERE CASE WHEN @OnlyOpen = 1 THEN Status ELSE 'N' END = 'N'

-- Same logic, sargable on Status
WHERE (@OnlyOpen = 0 OR Status = 'N')

For "optional filter" parameters like this, also keep parameter sniffing in mind: a single cached plan has to serve both @OnlyOpen = 0 and 1. OPTION (RECOMPILE) or two separate queries are the usual fixes.

Practical CASE Examples

Decode status codes

The simple CASE shown at the start: turn 'N', 'S', 'D' into readable text for a report.

Flag columns for analysis

SELECT OrderId,
       CASE WHEN ShippedDate IS NULL AND Status = 'N' THEN 1 ELSE 0 END AS IsOpen,
       CASE WHEN Amount >= 1000 THEN 1 ELSE 0 END                      AS IsKeyOrder
FROM #Orders;

0/1 flags are easy to sum, filter and feed into BI tools.

Guard against bad values per row

-- #Stock contains ('A', 10) and ('B', 0)
SELECT Item,
       CASE WHEN Qty = 0 THEN NULL ELSE 100 / Qty END AS Ratio
FROM #Stock;

For this specific case, 100 / NULLIF(Qty, 0) is shorter (see the alternatives below).

Fiscal periods

CASE WHEN MONTH(OrderDate) >= 10 THEN YEAR(OrderDate) + 1
     ELSE YEAR(OrderDate) END AS FiscalYear   -- fiscal year starting in October

When to Use CASE in Real-World Data Analysis

CASE is the right tool when the logic is local to the query: an ad-hoc banding for one report, a custom sort order, a conditional total, a one-off data fix. It is cheap (evaluated per row in memory, no extra I/O) and it keeps the logic visible right where it is used.

It becomes the wrong tool when:

Traps That Cause Real Errors

1. Data type precedence

SELECT CASE WHEN 1 = 0 THEN 1 ELSE 'none' END;
-- Msg 245: Conversion failed when converting the varchar value 'none' to data type int.

Because int has higher precedence than varchar, the whole CASE is typed int, and 'none' cannot be converted. The nasty part: as long as the condition is true, the ELSE value is never converted and the query runs fine. The error only appears once the data reaches the other branch, often in production. Make all branches the same type explicitly, for example CAST(1 AS varchar(10)).

2. Short-circuiting is not guaranteed for aggregates

CASE normally evaluates branches in order and stops at the first match, which makes per-row guards like WHEN Qty = 0 THEN NULL ELSE 100 / Qty safe. Aggregates are different: they are computed before the CASE sees their result.

-- #Stock contains ('A', 10) and ('B', 0)
SELECT CASE WHEN COUNT(*) > 0 THEN 'has rows'
            ELSE CAST(MIN(100 / Qty) AS varchar(10)) END
FROM #Stock;
-- Msg 8134: Divide by zero error encountered.

The ELSE branch is never chosen, but MIN(100 / Qty) is still computed over every row. Guard inside the aggregate instead: MIN(100 / NULLIF(Qty, 0)).

3. COALESCE with a subquery runs the subquery twice

COALESCE is shorthand for CASE, and SQL Server expands it into one: COALESCE((SELECT MAX(Qty) FROM #Stock), 0) becomes "CASE WHEN (subquery) IS NOT NULL THEN (subquery) ELSE 0 END". The execution plan shows two scans of #Stock. ISNULL with the same subquery scans it once. On small tables this does not matter; with an expensive subquery it does.

Replacements for the CASE Expression

Several built-in functions cover common CASE patterns in less code. Most of them are rewritten to CASE internally, so they behave the same way, including the nesting limit: eleven nested IIF calls raise the same error 125 as eleven nested CASEs. Their value is readability.

Instead of this CASEUseNotes
CASE WHEN Amount >= 1000 THEN 'Large' ELSE 'Normal' END IIF(Amount >= 1000, 'Large', 'Normal') One condition, two outcomes. SQL Server 2012+.
CASE n WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q2' ... END CHOOSE(DATEPART(quarter, OrderDate), 'Q1','Q2','Q3','Q4') Picks by 1-based index. An index outside the list returns NULL. SQL Server 2012+.
CASE WHEN ShippedDate IS NOT NULL THEN ShippedDate ELSE OrderDate END COALESCE(ShippedDate, OrderDate) or ISNULL(...) First non-NULL value. ISNULL takes two arguments and returns the type of the first; COALESCE takes many and is ANSI standard.
CASE WHEN Qty = 0 THEN NULL ELSE Qty END NULLIF(Qty, 0) The standard divide-by-zero guard: 100 / NULLIF(Qty, 0).
CASE WHEN a > b THEN a ELSE b END GREATEST(a, b) / LEAST(a, b) SQL Server 2022+ and Azure SQL. Ignores NULLs, unlike the CASE version, which returns b when a is NULL.
A long simple CASE that maps codes to labels Join to a lookup table Maintained as data, reusable everywhere, can carry more attributes (sort order, group, valid-from).
The same CASE in every query on a table A computed column or a view Defined once. A persisted computed column can even be indexed.
CASE WHEN a = b OR (a IS NULL AND b IS NULL) ... a IS NOT DISTINCT FROM b NULL-safe comparison, SQL Server 2022+. See IS DISTINCT FROM: comparing NULLs.

The lookup table, in practice

SELECT o.OrderId,
       ISNULL(s.StatusText, 'Unknown') AS StatusText
FROM #Orders AS o
LEFT JOIN (VALUES ('N', 'New'), ('S', 'Shipped'),
                  ('D', 'Delivered'), ('C', 'Cancelled')) AS s(Code, StatusText)
       ON s.Code = o.Status;

The inline VALUES list shows the idea; in a real system it is a permanent table (dbo.OrderStatus) with a foreign key from the orders table. The LEFT JOIN plus ISNULL plays the role of ELSE 'Unknown'.

Rule of thumb: if the mapping is a fact about your data (what does status 'S' mean?), store it in a table. If it is a question you are asking in this one query (which orders count as "large" for this report?), CASE is the right tool.

The Bottom Line

CASE is an expression that returns one value, usable anywhere a value is allowed. Use the searched form whenever conditions go beyond simple equality or involve NULL, order branches from specific to general, always think about what ELSE should return, and keep all branches on the same data type. Reach for IIF, CHOOSE, COALESCE, NULLIF or GREATEST when they say the same thing in less code, and move mappings that many queries share into a lookup table or computed column instead of copying the CASE around.

← Back to Blog