COALESCE in subquery still returns DBnull

Question

Unfortunately i am facing today another bug which i don't understand. I'm selecting the next and previous id from my database and i am using COALESCE to replace a null value with a number. But for some reason it still returns null, am i missing something? ``` "Select Gags.Title, Gags.DatePosted, Gags.Image, Users.Username, (Select Top(1) COALESCE(Id, 0) from Gags where Id > " + IdModel.Id + "), (Select Top(1) COALESCE(Id, 0) from Gags where Id < " + IdModel.Id + ") From Gags Inner Join Users on Gags.Userid = Users.Id where Gags.Id = " + IdModel.Id + " ``` Fixed Query: ``` Select Gags.Title, Gags.DatePosted, Gags.Image, Users.Username, isnull((Select Top(1) id from Gags where Id > " + IdModel.Id + "),-1), Isnull((Select Top(1) id from Gags where Id < " + IdModel.Id + "), -1) From Gags Inner Join Users on Gags.Userid = Users.Id where Gags.Id = " + IdModel.Id + " ``` I'm talking about the two sub queries. Thanks in advance! It's a terrible programming day today ..

Accepted Answer

For an empty rowset, `coalesce` won't be invoked:

```
(select top 1 coalesce(Id, 0) from ...)
```

And an empty rowset in a scalar context returns `null`:

```
select (select 1 where 1=0)
-->
NULL
```

One solution is to move the `coalesce` outside the subquery:

```
coalesce((select top 1 Id from ...),0)
```

COALESCE in subquery still returns DBnull

Solution

Problem