15

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 |
+----+---------------------+---------------------+

Why the first subquery does not return: t1.DateTest?

http://rextester.com/CNDOO40877

  • 3
    EXCELLENT use of a demo table and a repro query, by the way. I wasn't going to post an answer, but then I went, "He put all this work into the question, the least I could do is put some work into an answer, hahaha." – Brent Ozar Feb 23 '17 at 12:50
  • Hi @BrentOzar, thanks for your detailed answer, it is crystal clear. – McNets Feb 23 '17 at 13:58
16

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:

No Ferraris found

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

  • 3
    Hahaha, look carefully, are you sure there isn't any 360 Modena there? – McNets Feb 23 '17 at 14:04
  • 3
    @McNets I should probably go check again just to be safe. – Brent Ozar Feb 23 '17 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.