COALESCE with NULL

Question

I found this snippet of SQL in a view and I am rather puzzled by it's purpose (actual SQL shortened for brevity):

SELECT
    COALESCE(b.Foo, NULL) AS Foo

FROM a
LEFT JOIN b ON b.aId=a.Id

I cannot think of a single reason of the purpose of coalescing with null instead of just doing this:

SELECT
    b.Foo AS Foo

FROM a
LEFT JOIN b ON b.aId=a.Id

Or at the very least don't include the NULL explicitly:

SELECT
    COALESCE(b.Foo) AS Foo

FROM a
LEFT JOIN b ON b.aId=a.Id

I don't know who authored this (so I cannot ask), when it was authored, or for what specific MS SQL Server version it was written for (pre-2008 for sure though).

Is there any valid reason to coalesce with NULL instead of just selecting the column directly? I can't help but laugh and write it off as a rookie mistake but it makes me wonder if there is some "fringe case" that I don't know about.

Answer 1

You are right - there is no reason to use:

SELECT COALESCE(b.Foo, NULL)

...because if b.foo is NULL, you might as well just use:

SELECT b.foo

...assuming that you want to know if the value is null.

Answer 2

I think there might be an edge case, but it is very historic - it's a bit of a guess but it could surround the situation where b.foo = ' ' e.g. a string of spaces.

Go back far enough in SQL and LTrim(' ') returned null (6 / 6.5), so I am wondering whether Coalesce on an empty string also evaluated it to null, if it did then the mechanism was being used to turn strings of spaces only, into null values? (If all values evaluate as null, Coalesce will return Null.)

It's a guess and I can not test it right away, but shouldn't be hard to check.