T-SQL CASE Clause: How to specify WHEN NULL

CASE WHEN last_name IS NULL THEN '' ELSE ' '+last_name END

The WHEN part is compared with ==, but you can't really compare with NULL. Try

CASE WHEN last_name is NULL  THEN ... ELSE .. END

instead or COALESCE:

COALESCE(' '+last_name,'')

(' '+last_name is NULL when last_name is NULL, so it should return '' in that case)

There are plenty of solutions but none covers why the original statement doesn't work.

CASE last_name WHEN null THEN '' ELSE ' '+last_name

After the when, there is a check for equality, which should be true or false.

If one or both parts of a comparison is null, the result of the comparison will be UNKNOWN, which is treated like false in a case structure. See: https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

To avoid this, Coalesce is the best way.

Given your query you can also do this:

SELECT first_name + ' ' + ISNULL(last_name, '') AS Name FROM dbo.person

The problem is that null is not considered equal to itself, hence the clause never matches.

You need to check for null explicitly:

SELECT CASE WHEN last_name is NULL THEN first_name ELSE first_name + ' ' + last_name