Wondering if anyone has an opinion on the following. I have query where I need to eliminate the data in a field under a certain condition: if the quantity field of a record is 0 then I don't need to see the location field of that same record.
I've set up a case statement as such:
case when quantity = 0 then NULL when quantity > 0 then [Location] end) as [Location]
Then I saw if/else and set this up:
if quantity > 0 then [Location] else NULL endif as [Location]
They appear to pull and display the same data. Is there a benefit to either?
asked 01 Mar '11, 16:22
In general, I would prefer
That's comparable to the question whether to use if or switch/case in C/C++ and similar programming languages.
So, for your sample, I would suggest to use the if-expression.
Note that there's another variant of the case-expression (though not possible in your sample):
This does test expression0 against a list of expressions (expression1, expression3, ...) and is somewhat shorther than the CASE WHEN search-condition ... syntax.
Just to add:
When the expression in the ELSE clause (both with IF and CASE) should return NULL (as in your example), then you can leave out the ELSE clause - omiting it is identical to ELSE NULL. So your expression could be reduced to:
As always, leaving out the ELSE part may decrease the readibility based on whether one is used to the default NULL result or not.
Just to add one big difference between IF and CASE expressions with ELSE clauses I came about based on Siger's FAQ on CASE exporessions with NULL values:
A simple repro:
begin declare n int = null; select if n = 1 then 'true' else 'false' end if, case n when 1 then 'true' else 'false' end case, case when n = 1 then 'true' else 'false' end case; end;
returns NULL, 'false', 'false'
answered 08 Aug '13, 03:56