Why COALESCE within a subquery returns NULL?

Given this schema:

CREATE TABLE #TEST_COALESCE
(
    Id int NOT NULL,
    DateTest datetime NOT NULL,
    PRIMARY KEY (Id, DateTest)
);

INSERT INTO #TEST_COALESCE VALUES
(1, '20170201'),
(1, '20170202'),
(1, '20170203'),
(2, '20170204'),
(2, '20170205'),
(2, '20170206');

If I use COALESCE within a subquery, it returns NULL.

SELECT  t1.Id, t1.DateTest,
        (SELECT TOP 1 COALESCE(t2.DateTest, t1.DateTest)
         FROM         #TEST_COALESCE t2
         WHERE        t2.Id = t1.Id
         AND          t2.DateTest > t1.DateTest
         ORDER BY     t2.Id, t2.DateTest) NextDate
FROM    #TEST_COALESCE t1;

+----+---------------------+---------------------+
| Id | DateTest            | NextDate            |
+----+---------------------+---------------------+
| 1  | 01.02.2017 00:00:00 | 02.02.2017 00:00:00 |
| 1  | 02.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 1  | 03.02.2017 00:00:00 | NULL                |
| 2  | 04.02.2017 00:00:00 | 05.02.2017 00:00:00 |
| 2  | 05.02.2017 00:00:00 | 06.02.2017 00:00:00 |
| 2  | 06.02.2017 00:00:00 | NULL                |
+----+---------------------+---------------------+

However, if it is placed outside the subquery:

```
SELECT t1.Id, t1.DateTest,
COALESCE((SELECT TOP 1 t2.DateTest
FROM #TEST_COALESCE t2
WHERE t2.Id = t1.Id
AND t2.DateTest > t1.DateTest
ORDER BY t2.Id, t2.DateTest), t1.DateTest) NextDate
FROM #TEST_COALESCE t1;

+----+---------------------+---------------------+
| Id | DateTest | NextDate |
+----+---------------------+---------------------+
| 1 | 01.02.2017 00:00:00 | 02.02.2017 00:00:00 |
| 1 | 02.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 1 | 03.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 2 | 04.02.2017 00:00:00 | 05.02.2017 00:00:00 |
| 2 | 05.02.2017 00:00:00 | 06.02.2017 00:00:00 |
| 2 | 06.02.2017 00:00:00 | 06.02.2017 00:00:00 |
+----+------------------

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 > '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...

StackExchange Database Administrators Q#165295, answer score: 18