SQL CASE statement with OR?

Question

Say I have to following:

Select OrderID = 
    Case OrderID 
        When 1 Then 'Customer1'
        When 2 Then 'Customer2'
        When 3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Is it possible to add an or and do something along the lines of:

Select OrderID = 
    Case OrderID 
        When 1 Then 'Customer1'
        When 2 Or 3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

@AnthonyRaymond Well I obviously tried my exact Code and got the error on the Or. I wasn't asking if my code would work, I was asking how I would do it. — user2125348, May 20 '14 at 19:58

score 27 · Accepted Answer · answered May 20 '14 at 19:57

27

There are two forms of CASE expression, 'searched' and 'simple'. You can't use an OR with a 'simple' CASE expression, but you can with the 'searched' form:

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID = 2 Or
         OrderID = 3 Then 'Customer2'
    Else 'Unknown Customer'
End

Or even

Case  
    When OrderID = 1 Then 'Customer1'
    When OrderID IN (2, 3) Then 'Customer2'
    Else 'Unknown Customer'
End

answered May 20 '14 at 19:57

p.s.w.g

146,324
30
291
331

But is this going to be optimized? Or will it have to pull `OrderID` inside of each case? My though process was the method I was using was Grabbing OrderID once, and using that temp variable to test against. Are these methods now going to have to pull OrderID for each test? Would I be better of leaving my original code? Or am I complelty wrong and the server handles all of these the same? – user2125348 May 20 '14 at 20:01
even furthur more from the Documentation of MSDN `a simple CASE expression allows for only an equality check; no other comparisons are made` when your answer seems to be more `searched CASE expression allows for values to be replaced in the result set based on comparison values`, Would it not be slower using a comparison vs equality check? – user2125348 May 20 '14 at 20:06
From the structure that you've showed us so far I believe it will only 'pull' the column once when analyzing each result row, regardless of how many times it appears in the `SELECT` clause. However, I'll admit that I'm not an expert on SQL Server optimization, so there might be some subtleties to this that I'm not aware of. – p.s.w.g May 20 '14 at 20:07
@user2125348 Also, `OrderID = 1` and `OrderID IN (2, 3)` *are* equality checks, but yes, a searched `CASE` expression may be slower in some situations since it will have to go through each `WHEN` expression until it finds a match, whereas the the simple `CASE` could implement something like a [branch table](http://en.wikipedia.org/wiki/Branch_table) for speed instead. It might also be faster in a few situations because you have tighter control over the order of execution. – p.s.w.g May 20 '14 at 20:13

score 4 · Answer 2 · answered May 20 '14 at 19:57

4

You can use alternative form of CASE

Select OrderID = 
    Case 
        When OrderID = 1 Then 'Customer1'
        When OrderID = 2 Or OrderID = 3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

answered May 20 '14 at 19:57

Yuriy Galanter

38,833
15
69
136

score 2 · Answer 3 · answered May 20 '14 at 19:57

2

CASE expression has two kinds of syntax - the simple (i.e. one that you are showing), and the searched, with multiple logical conditions. If you would like to use an OR, you need the second kind:

Select OrderID = 
    Case 
        When OrderID =1 Then 'Customer1'
        When OrderID =2 Or OrderID =3 Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

Here is the description from the documentation:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.
The searched CASE expression evaluates a set of Boolean expressions to determine the result.

answered May 20 '14 at 19:57

Sergey Kalinichenko

714,442
84
1,110
1,523

So, my original example would be the best performing as it is an equality check and not a Boolean comparison? Even if only for this small example? – user2125348 May 20 '14 at 20:09
1

@user2125348 The difference in `CASE` statements would have no discernible difference, because it does not change the number of I/Os required to complete the query. [See this answer for more info on the performance impact of `CASE` expressions in SQL](http://stackoverflow.com/a/12012987/335858). – Sergey Kalinichenko May 20 '14 at 20:17

score 1 · Answer 4 · edited May 20 '14 at 20:23

1

Try this. This would work for your scenario.

Select OrderID = 
    Case
        When OrderID  = 1 Then 'Customer1'
        When OrderID in (2, 3) Then 'Customer2'
        Else 'Unknown Customer'
    End 
From OrdersPlaced

edited May 20 '14 at 20:23

Zero Piraeus

56,143
27
150
160

answered May 20 '14 at 20:01

Siddhardha

21
2

SQL CASE statement with OR?

4 Answers4