Case with NULL

#1

Why can I do:

SELECT PersonID = CASE WHEN o.ID IS NULL THEN o2.ID ELSE o.ID END

SELECT PersonID = CASE WHEN o.ID IS NOT NULL THEN o2.ID ELSE o.ID END

SELECT PersonID = CASE o.ID WHEN NULL THEN o2.ID ELSE o.ID END

But not:

SELECT PersonID = CASE o.ID WHEN NOT NULL THEN o2.ID ELSE o.ID END

I get an error:

Incorrect syntax near the keyword 'NOT'.

and

SELECT PersonID = CASE o.ID WHEN IS NOT NULL THEN o2.ID ELSE o.ID END

I get an error:

Incorrect syntax near the keyword 'IS'.

Using this syntax, can I not test for 'NOT NULL'?

Thanks,

Tom

#2

On 2011-01-13 04:12, tshad wrote:
[...]

Quote:

SELECT PersonID = CASE o.ID WHEN NULL THEN o2.ID ELSE o.ID END

I'm a bit surprised that this works. This case construction uses "=" and
nothing (including null) equals null. FWIW, this should be equal to:

SELECT coalesce(o.ID, o2.ID)

Quote:

But not:

SELECT PersonID = CASE o.ID WHEN NOT NULL THEN o2.ID ELSE o.ID END

I get an error:

Incorrect syntax near the keyword 'NOT'.

and

SELECT PersonID = CASE o.ID WHEN IS NOT NULL THEN o2.ID ELSE o.ID END

There is another CASE construction (my windows vm is down so I can't
verify whether it is supported by sqlserver)

CASE WHEN o.ID IS NOT NULL THEN o2.ID ELSE ...

/Lennart

#3

tshad (tfs (AT) dslextreme (DOT) com) writes:

Quote:

Why can I do:
SELECT PersonID = CASE o.ID WHEN NULL THEN o2.ID ELSE o.ID END

Just because you can, does not mean that you should. This experssion
will always return o.ID, because comparisons will NULL always yield
UNKNOWN.

You should write this as

CASE WHEN o.ID IS NULL THEN o2.ID ELSE o.ID END

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4

Hi Tom,

The CASE expression has two syntaxes:

1:
CASE
WHEN <logic expression> THEN <scalar expression>
{ WHEN <logic expression> THEN <scalar expression> ... }
[ ELSE <scalar expression> ]
END

2:
CASE <scalar expression A>
WHEN <scalar expression> THEN <scalar expression>
{ WHEN <scalar expression> THEN <scalar expression> ... }
[ ELSE <scalar expression> ]
END

The second is defined to be equivalent to the former, where each WHEN
<scalr expression> is replaced by WHEN <scalar expression A> = <scalar
expression>.

Quote:

SELECT PersonID = CASE o.ID WHEN NULL THEN o2.ID ELSE o.ID END

As a result, the expression above is valid syntax (both o.ID and NULL
are valid scalar expressions) - but nonsense. A comparison with NULL
will never evaluate to True, so this whole fragment is logically
equivalent to
SELECT o.ID

Quote:

SELECT PersonID = CASE o.ID WHEN NOT NULL THEN o2.ID ELSE o.ID END
and
SELECT PersonID = CASE o.ID WHEN IS NOT NULL THEN o2.ID ELSE o.ID END

These are both invalid syntax, because neither NOT NULL nor IS NOT
NULL is a valid scalar expression.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#5

"Lennart Jonsson" <erik.lennart.jonsson (AT) gmail (DOT) com> wrote

Quote:

On 2011-01-13 04:12, tshad wrote:
[...]

SELECT PersonID = CASE o.ID WHEN NULL THEN o2.ID ELSE o.ID END

I'm a bit surprised that this works. This case construction uses "=" and
nothing (including null) equals null. FWIW, this should be equal to:

SELECT coalesce(o.ID, o2.ID)

Is one better than the other?

SELECT PersonID = coalesce(o.ID, o2.ID)

or

SELECT PersonID = CASE WHEN o.ID IS NULL THEN o2.ID ELSE o.ID END

Thanks,

Tom

Quote:

But not:

SELECT PersonID = CASE o.ID WHEN NOT NULL THEN o2.ID ELSE o.ID END

I get an error:

Incorrect syntax near the keyword 'NOT'.

and

SELECT PersonID = CASE o.ID WHEN IS NOT NULL THEN o2.ID ELSE o.ID END

There is another CASE construction (my windows vm is down so I can't
verify whether it is supported by sqlserver)

CASE WHEN o.ID IS NOT NULL THEN o2.ID ELSE ...

/Lennart

#6

On 2011-01-13 19:54, tshad wrote:

Quote:

"Lennart Jonsson" <erik.lennart.jonsson (AT) gmail (DOT) com> wrote in message
news:igmca7$b1b$1 (AT) news (DOT) eternal-september.org...
On 2011-01-13 04:12, tshad wrote:
[...]

SELECT PersonID = CASE o.ID WHEN NULL THEN o2.ID ELSE o.ID END

I'm a bit surprised that this works. This case construction uses "=" and
nothing (including null) equals null. FWIW, this should be equal to:

SELECT coalesce(o.ID, o2.ID)

Is one better than the other?

SELECT PersonID = coalesce(o.ID, o2.ID)

or

SELECT PersonID = CASE WHEN o.ID IS NULL THEN o2.ID ELSE o.ID END

I prefer coalesce over case because - IMO - it is easier to read, not to
mention that there is less typing involved. Other than that I don't
think [1] there are any differences.

[1] I have about 1 day experience with sql server, so there might be a
thing or two that I haven't discovered yet ;-)

/Lennart

#7

tshad (tfs (AT) dslextreme (DOT) com) writes:

Quote:

Is one better than the other?

SELECT PersonID = coalesce(o.ID, o2.ID)

or

SELECT PersonID = CASE WHEN o.ID IS NULL THEN o2.ID ELSE o.ID END

As Lennart says, the former is shorter to type. The algebrizer
rewrites coalesce to a CASE expression, so they are very equivalent.

But you could claim that CASE is better, because it is more honest.
Say that you write:

coalesce((SELECT ....), 4711)

Looks good. But this is the true story:

CASE WHEN (SELECT ...) IS NOT NULL THEN (SELECT ...) ELSE 4711 END

Personally, I use coalesce all the time.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#8

On Thu, 13 Jan 2011 22:49:06 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

tshad (tfs (AT) dslextreme (DOT) com) writes:
Is one better than the other?

SELECT PersonID = coalesce(o.ID, o2.ID)

or

SELECT PersonID = CASE WHEN o.ID IS NULL THEN o2.ID ELSE o.ID END

As Lennart says, the former is shorter to type. The algebrizer
rewrites coalesce to a CASE expression, so they are very equivalent.

But you could claim that CASE is better, because it is more honest.
^^^^^^

Not the adjective I would have picked. Is there a technical
difference that matters?

Quote:

Say that you write:

coalesce((SELECT ....), 4711)

Looks good. But this is the true story:

CASE WHEN (SELECT ...) IS NOT NULL THEN (SELECT ...) ELSE 4711 END

Personally, I use coalesce all the time.

Get the computer to do your lying for you? <BEG>

Sincerely,

Gene Wirchenko

#9

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:

Quote:

Not the adjective I would have picked.

You're not Swedish, are you? :-)

Quote:

Is there a technical difference that matters?

Well, it was the one I used:

Quote:

Say that you write:

coalesce((SELECT ....), 4711)

Looks good. But this is the true story:

CASE WHEN (SELECT ...) IS NOT NULL THEN (SELECT ...) ELSE 4711 END

Personally, I use coalesce all the time.

When you use CASE, you easier realise that the subquery is computed
twice.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

#10

On Fri, 14 Jan 2011 23:13:32 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Quote:

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Not the adjective I would have picked.

You're not Swedish, are you? :-)

No, why?

Quote:

Is there a technical difference that matters?

Well, it was the one I used:

Say that you write:

coalesce((SELECT ....), 4711)

Looks good. But this is the true story:

CASE WHEN (SELECT ...) IS NOT NULL THEN (SELECT ...) ELSE 4711 END

Personally, I use coalesce all the time.

When you use CASE, you easier realise that the subquery is computed
twice.

And only once with coalesce() which would argue in favour of
using it. (Naturally, all bets are off when optimisation enters the
picture.)

Sincerely,

Gene Wirchenko