How to Use OR Condition in CASE WHEN Statement? – Interview Question of the Week #102

A friend was asked in an interview whether OR can appear inside CASE WHEN. I do not love questions that rely on a trick of wording, but this one reveals a useful distinction. My earlier answer made it sound as though OR was impossible in CASE. That was too broad: it is valid in a searched CASE condition.

Simple CASE compares values

The first form compares one expression with each WHEN value. Here is the original mapping, kept because it makes the distinction easy to see:

DECLARE @TestVal int = 3;

SELECT CASE @TestVal
           WHEN 1 THEN 'First'
           WHEN 2 THEN 'Second'
           WHEN 3 THEN 'Third'
           ELSE 'Other'
       END AS ValueLabel;
GO

This returns Third. A simple CASE is a good fit when one value must be compared for equality with several alternatives. A Boolean expression joined by OR is not a single comparison value for that form.

Searched CASE accepts OR

Leave the expression off the opening CASE and put a condition after WHEN. That condition can combine predicates with OR or AND.

DECLARE @TestVal int = 5;

SELECT CASE
           WHEN @TestVal < 0 OR @TestVal > 3 THEN 'Outside range'
           ELSE 'Within range'
       END AS RangeLabel;

For this value the result is Outside range. The same syntax works when the two predicates inspect different columns. Use parentheses when mixing AND and OR so the intended logic is clear.

So the short interview answer is: yes, OR works in a searched CASE WHEN. A simple CASE performs value comparisons instead. For more examples, see my earlier CASE expression explanation and IF and THEN discussion.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.