Sql Coalesce

[mendhak]

What is "COALESCE"? This is most likely in T-SQL.

[Danial]

Originally posted by mendhak
What is "COALESCE"? This is most likely in T-SQL.

Never heard of it, But here is the MSDN explanation of it

http://msdn.microsoft.com/library/de...ca-co_9dph.asp

[szlamany]

Coalesce is an ansi-standard-sql function.

It returns the first non-NULL value in it's arguments.

MS T-SQL has an IsNull function that works like Coalesce with "two-arguments".

IsNull({some expression},'EXP IS NULL') will return one of two things:

1) Return {some expression} if it is not null
2) Otherwise returns the string 'EXP IS NULL'

Coalesce({some expression},'EXP IS NULL') will do exactly the same thing...

But:

Coalesce({some exp1},{some exp2},{some exp3},'ALL EXP ARE NULL')

will return the first expression that is not null, exp1, exp2, exp3 or return the final argument 'ALL EXP ARE NULL'

I've been dis'ed by purist SQL folks for using ISNULL - but I can't type COALESCE as easily as ISNULL, so I prefer ISNULL - even if it won't work on all SQL platforms...

A perfect example of where we use ISNULL is...

SELECT ...
,ISNULL(TE.TEACHERNAME+' ('+CL.TEAID+')',CL.TEAID)
... FROM CLASSTABLE CL
LEFT JOIN TEACHERTABLE TE ON TE.TEAID=CL.TEAID

Here we join a teacher table to a class table - if the teacher doesn't exist in the teacher table, then TE.TEACHERNAME is null - making the whole expression NULL - so we return just the CL.TEAID in that case.