Or is not supported with CASE Statement in SQL Server

sql

best-practices

performance

data-type

byAnton Shumikhin·Mar 1, 2025

In SQL, CASE statements direct usage of OR is a no-go. Opt for multiple WHEN clauses or use IN for a list of values instead. Consider this:

SELECT CASE 
         WHEN condition1 THEN result
         WHEN condition2 THEN result  -- Two WHENs walk into a bar, OR stayed outside.
       END AS ColumnAlias
FROM Table;

Alternatively, leverage IN for compact syntax:

SELECT CASE 
         WHEN ColumnName IN (value1, value2) THEN result 
       END AS ColumnAlias
FROM Table;

These workarounds reflect the SQL's CASE statement's versatility.

Using 'IN' operator

For value checks against a single column, the IN operator is your Swiss Army knife. Here you go:

SELECT 
  CASE 
    WHEN columnValue IN (expectedValue1, expectedValue2, expectedValue3) THEN 'Match' 
    ELSE 'Brushing off the dust and moving on!' 
  END AS Outcome
FROM YourTable;

Default values using 'ELSE'

Kick in the 'Plan Busing anELSE` clause when no conditions match. Because SQL also believes in "If all else fails, try, try again":

SELECT 
  CASE
    WHEN condition THEN result
    -- ... other conditions ...
    ELSE defaultResult  -- No match? No worries! 
  END AS ColumnAlias
FROM Table;

Conditional sorting

CASE in the ORDER BY brings custom sorting to your fingertips. Here's a quick peek:

SELECT Column1, Column2
FROM Table
ORDER BY 
  CASE 
    WHEN Column1 = 'Condition' THEN 1
    ELSE 2 -- The second mouse gets the cheese!
  END;

Conditional aggregation

CASE even joins you in the HAVING clause to filter grouped data:

SELECT Column, COUNT(*)
FROM Table
GROUP BY Column
HAVING 
  COUNT(CASE WHEN AnotherColumn = 'Condition' THEN 1 ELSE NULL END) > threshold;  -- NULL to the rescue!

Working with Boolean logic

Although direct support for OR is missing, you can freely weave in Boolean logic within WHEN clauses. Look, no hands, AND and OR :

SELECT 
  CASE 
    WHEN conditionA1 AND (conditionB1 OR conditionB2) THEN result1
    -- More conditions...
    ELSE defaultResult // Just a trampoline to fall back! 
  END AS ResultColumn
FROM Table;

Performance enhancements

Give prominence to the most probable conditions first to enhance query performance:

SELECT 
  CASE 
    WHEN mostLikelyCondition THEN 'Most likely outcome'
    WHEN nextMostLikelyCondition THEN 'Next likely outcome'
    -- More conditions...
    ELSE 'Least likely outcome' // Better luck next time! 
  END AS ResultColumn
FROM Table;

Data type considerations

Ensure data types of WHEN clause results are either a match or are implicitly convertible:

SELECT 
  CASE 
    WHEN condition THEN 'StringResult' -- VARCHAR
    WHEN anotherCondition THEN 123 -- INT, with a slight identity crisis to be VARCHAR
    ELSE NULL -- NULL, the consummate shape shifter!
  END AS VarCharResultColumn
FROM Table;