Why does my query to combine columns return NULL? (SQL on StackExchange DataExplorer )

Question

I have written a StackExchange DataExplorer query to list all comments by User.Id The query works and returns Ids of questions and answers. What I do not understand is why, for answers, the second column is empty.

DECLARE @UserId int = ##UserId##

Select p.Id
   , '<a href=https://stackoverflow.com/questions/' 
          + Cast(p.Id as varchar(20)) + '>'           
          + Cast(p.Id as varchar(20)) 
          + ' - ' + p.Title + '</a>'
   , c.Text
      FROM Users u            
      Join Comments c ON c.UserId = @UserId
      JOIN Posts p ON p.Id = c.PostId
      where u.Id = @UserId AND p.Id IS NOT NULL

Even assuming that the column p.Title is NULL the column p.Id is not NULL and I would therefore expect that this part

'<a href=https://stackoverflow.com/questions/' 
              + Cast(p.Id as varchar(20)) + '>'           
              + Cast(p.Id as varchar(20)) 
              + ' - ' + p.Title + '</a>'

would return something as per this question. But the second column is totally empty.

Why is that the case?

Answer 1

Even assuming that the column p.Title is NULL

Which it is for those rows.

the column p.Id is not NULL and therefore i would expect [the result to be something not null]

Nope. If you concatenate NULL with anything in SQL Server using the + operator then you end up getting NULL except if concat_null_yields_null is OFF.

You can use the CONCAT function instead. This also saves the need to CAST

DECLARE @UserId INT = ##UserId##

SELECT p.Id,
       CONCAT('<a href=http://stackoverflow.com/questions/', 
              p.Id, 
              '>',
              p.Id, 
              ' - ',
              p.Title COLLATE SQL_Latin1_General_CP1_CI_AS, 
              '</a>'),
       c.Text
FROM   Users u
       JOIN Comments c
         ON c.UserId = @UserId
       JOIN Posts p
         ON p.Id = c.PostId
WHERE  u.Id = @UserId
       AND p.Id IS NOT NULL