Tuesday, January 10, 2006

Null coalescing operator

Fancy title to get things going.

This is one of those things that'll be second nature once I start using it in anger, but until I do that, I'll consistently forget it.

C# 2.0 introduces nullable types, and the null coalescing operator (??) is a binary operator that works pretty much the same way as T-SQL's ISNULL() function.

So for:
a ?? b

think:
ISNULL(a, b)

One small caveat here is that b could of course be a nullable type as well, in which case the result of a ?? b is still a nullable type. If it's not, the result isn't either. See example below (ripped from C# 2.0 spec).

int? x = GetNullableInt();
int? y = GetNullableInt();
int? z = x ?? y;
int i = z ?? -1;

5 comments:

John Hartnup said...

This probably makes more sense to people who know what SQL's ISNULL does -- I didn't, and it's not intuitive.

My understanding is that C#'s:

i = x ?? y;

.. is equivalent to ..

i = x.HasValue ? x : y ;

or for the ternary operator unaware

if(x.HasValue) { i = x } else { i = y }

Can you chain these? It feels as if you should be able to:

i = x ?? y ?? z ;

Stephen Salt said...

You can indeed chain them. ?? is right-associative, so your example of

i = x ?? y ?? z;

equates to

i = x ?? (y ?? z);

Conceptually, if you have a long chain of these, you'll get the value of the first non-null one, or null if they're all null.

Phil said...

You mean COALESCE, ISNULL is a) SQL Server specific, and b) non chainable.

Though I'm not sure of the usefulness of this, unless it can also handle DBNull, string.Empty etc.

David Hayes said...

Interesting, I haven't played much with C# 2.0 yet (still stuck on 1.1 for my day job) and my beta has expired :(. I should really get around to looking at the assorted 'express' versions.

Nullable types are something I'm really looking forward too (having just had to write several by hand) will be interested to see how useful the implemetation is

Stephen Salt said...

Actually ISNULL() is chainable (at least on SQL Server 2005), so long as you use it in the same fashion as you would with the C# code, i.e. by repeating the "operator" (in this case ISNULL()).

So

ISNULL(a, ISNULL(b, c))

is equivalent (and I hope I've got my ordering right here) to

a ?? b ?? c

However I fully agree that in the SQL case, COALESCE(a, b, c) is much nicer.

In deference to the MS-only side of things, I have changed the original post to read T-SQL instead :)