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?

Thank you.

asked 01 Mar '11, 16:22

zippidydo's gravatar image

accept rate: 0%

In general, I would prefer

  • if-expressions for typical boolean comparisons and
  • case-expressions for more than two cases.

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):

CASE expression0
WHEN expression1 THEN expression2, ...
WHEN expression3 THEN expression4, ...
                 ELSE expressionX

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:

if quantity > 0 then [Location] endif as [Location]

As always, leaving out the ELSE part may decrease the readibility based on whether one is used to the default NULL result or not.

permanent link

answered 01 Mar '11, 17:30

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 02 Mar '11, 09:13

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:

  • With IF expressions, if the condition is UNKNOWN, the IF expression returns NULL - it does not return the value from the ELSE clause (which has been a surprise to me now and then...).

  • With CASE expressions, if expression0 is NULL, then the value from the ELSE clause will be returned.

A simple repro:

   declare n int = null;
      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;

returns NULL, 'false', 'false'

permanent link

answered 08 Aug '13, 03:56

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 01 Mar '11, 16:22

question was seen: 1,299 times

last updated: 08 Aug '13, 03:56