Why COALESCE within a subquery returns NULL?
Things in the select are returned only if there are rows returned in the FROM statement. 

First, let's think of it conceptually.

Query 1 is like:


  "Go find all of the Ferraris in your garage. For each Ferrari, give me
  the license plate number, or if it doesn't have a plate number, give
  me 'NO FERRARIS FOUND.'"


The query would come back with no rows - because there wasn't a Ferrari in the garage. (At least, there weren't any rows found in my own garage.)

Query 2 is different:


  "Go to the garage. IF you find a license plate on a Ferrari, give me
  that - otherwise, give me 'NO FERRARIS FOUND.'"


That's why the coalesce has to be outside of the search operation: you need it to happen even when there's no rows in the result set.

Now, let's look at your query.

I'm going to take the subquery out on its own, and I'm going to hard-code values for one of the rows where you want the COALESCE to work, but it can't:

SELECT TOP 1 COALESCE(t2.DateTest, 'NO FERRARIS FOUND')
     FROM         #TEST_COALESCE t2
     WHERE        t2.Id = 1
     AND          t2.DateTest &gt; '2017-02-03 00:00:00.000'
     ORDER BY     t2.Id, t2.DateTest


In the WHERE clause, I've hard-coded Id = 1 and DateTest > '2017-02-03 00:00:00.000'. When this query runs, it returns no results:



That's why the COALESCE doesn't work: there were no rows in this result set, and no Ferraris in your garage. Master that concept, and you will have Ferraris in your...wait a minute...I've mastered that concept, and there are no Ferraris in my garage...