Why nulls are appearing and proper reference of a combined variable

In the following code

SELECT x.[First Name] + ' ' + x.[Last Name] AS 'Combined Name'
FROM [Database1].[dbo].[InfoTable] x
WHERE 'Combined Name' IS NOT NULL

Combined table still pops up with Nulls.

Questions: Why? Is it because of the WHERE statement? Is it only acceptable to use fully qualified names such as

SELECT x.[First Name] + ' ' + x.[Last Name] AS 'Combined Name'
FROM [Database1].[dbo].[InfoTable] x
WHERE x.[First Name] + ' ' + x.[Last Name] IS NOT NULL

Things I’ve tried..different naming conventions and syntax, etc. The fully qualified names version works as expected, hence the question..just curious if I can use a shorter syntax that isn’t completely spelled out. (In this example code was borrowed from a statement with multiple joins, so assume there are other tables in play)

Answer

This condition:

WHERE 'Combined Name' IS NOT NULL

Is comparing the constant string 'Combined Name' to NULL. It is evidently not NULL so this never returns false.

I would recommend that you never use single quotes to refer to column names — too easily confused with strings as you do here. The best bet is to choose names, such as combined_name that do not need to be escaped.

Oh, the answer to what you want to do is that you need a CTE, subquery, or lateral join, because you cannot refer to the column name in a where. I might suggest:

SELECT v.combined_name
FROM [Database1].[dbo].[InfoTable] x CROSS APPLY
     (VALUES (x.[First Name] + ' ' + x.[Last Name])
     ) v(combined_name)
WHERE v.combined_name IS NOT NULL;