Why do I get either zero rows or a row with a NULL value, just by changing the SELECT clause?

Question

I bumped into something unexpected just now, and it reminded me that there is still much I do not know about SQL.

I have a reporting query which uses UNION ALL to concatenate results for different date ranges and store them, in HTML form, in a temp table. The query gave a NULL value for the MTD range today, as there was no activity on the 1st. What I don't understand is: why did I get any row at all? Toying with the query, I see that this snip:

SELECT
    70000,
    '<tr>' + dbo.FormatAsHTMLRowHead('Month to date') + '</tr>'
FROM
    #RawResults
WHERE
    Date = '2011-05-01'

...gives no results, zero rows, as expected. However, if I add a field to the SELECT clause:

SELECT
    70000,
    '<tr>'
    + dbo.FormatAsHTMLRowHead('Month to date')
    + dbo.FormatAsHTMLCell(dbo.FormatInteger(SUM(Leads)), 'R')
    + '</tr>'
FROM
    #RawResults
WHERE
    Date = '2011-05-01'

Then I get a single row with a NULL value:

Sequence HTML
-------- ----
70000    NULL

Querying SELECT Field FROM #RawResults WHERE Date = '2011-05-01' returns no results, so it seems that a UDF acting on a non-literal causes SQL to return a row even when there's no matching value in the WHERE clause. Is this some weird edge case, or does this make perfect sense for some reason that escapes me this morning?

Answer 1

The 2nd one has an aggregate SUM without a GROUP BY = always one row returned

See my answer here: Does COUNT(*) always return a result?