sql - COALESCE Function in TSQL

Question

Can someone explain how the COALESCE function in TSQL works? The syntax is as follows

COALESCE(x, y)

The MSDN document on this function is pretty vague

Answer 1

I'm not sure why you think the documentation is vague.

It simply goes through all the parameters one by one, and returns the first that is NOT NULL.

COALESCE(NULL, NULL, NULL, 1, 2, 3)
=> 1


COALESCE(1, 2, 3, 4, 5, NULL)
=> 1


COALESCE(NULL, NULL, NULL, 3, 2, NULL)
=> 3


COALESCE(6, 5, 4, 3, 2, NULL)
=> 6


COALESCE(NULL, NULL, NULL, NULL, NULL, NULL)
=> NULL

It accepts pretty much any number of parameters, but they should be the same data-type. (If they're not the same data-type, they get implicitly cast to an appropriate data-type using data-type order of precedence.)

It's like ISNULL() but for multiple parameters, rather than just two.

It's also ANSI-SQL, where-as ISNULL() isn't.