CODEWITHSUNDEEP
sqlsql-servert-sql
user3779002
user3779002

Reputation: 620

Why does COALESCE not return null here?

I was bitten by a coalesce-returns-null issue before, that's covered by this https://dba.stackexchange.com/questions/165295/why-coalesce-within-a-subquery-returns-null.

To improve my understanding I wanted a simpler example:

create table tbl(col int);

then

SELECT col
FROM tbl
-- returns nothing of course, tbl is empty

So in this following code, coalesce has a nothing-there to coalesce with, so it should return either nothing (no result set), or perhaps a single null.

select coalesce(
(
    SELECT col
    FROM tbl
), 22)

But run it and I get 22. Why does it work when I think it shouldn't?

(tested in SQL Server/TSQL)

Upvotes: 1

Views: 2457

Answers (3)

Charlieface
Charlieface

Reputation: 72491

The result of a subquery used in an outer select or comparison operator must return exactly 1 column and 0 or 1 rows. If it returns more it is an error. But if it returns 0 values, it is converted to a null expression. Therefore:

(
    SELECT col
    FROM tbl
)

inside an outer select evaluates to null.

Placing that inside the coalesce means that you now have null and 22. So 22 is returned.

select coalesce(
(
    SELECT col
    FROM tbl
), 22)

Whereas if the coalesce was inside the subquery, the semantic is completely different. Here, the subquery will return either 0 or 1 results. If it has a result then it will be coalesced with 22. But if there is no result then it can't be coalesced, therefore a null will be returned to the outer query.

select 
(
    SELECT coalesce(col, 22)
    FROM tbl
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In this query:

select coalesce( (SELECT col FROM tbl), 22)
-----------------^ subquery

The subquery is a scalar subquery. That is, it is being used in place of a constant. A scalar subquery has the following properties:

  • Returns one column.
  • Returns zero or one rows.
  • If it returns zero rows, then the value is NULL.

The third of these is why you get NULL so COALESCE() returns the second argument.

Note that if tbl had more than one row, then you would get an error, presumably of the form "scalar subquery returns more than one row". This would be true even if the values in col2 were all NULL.

Upvotes: 5

eshirvana
eshirvana

Reputation: 24633

because when coalesce evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL !

what you are looking for is :

select coalesce(col, 22)
from tbl

Upvotes: 0

Related Questions