CODEWITHSUNDEEP
sqlcoalesce
t_warsop
t_warsop

Reputation: 1270

Why does COALESCE execute the subquery twice?

I'm currently learning about the differences between ISNULL and COALESCE and I've come across a statement saying:

COALESCE((<subquery), 0)

is translated to (according to the SQL standard):

CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END

My question is: why is the subquery executed twice? This seems inefficient.

Upvotes: 0

Views: 627

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

ISNULL() is associated with SQL Server, so this question appears to be about SQL Server. As pointed out in the comments, SQL Server runs the subquery twice.

By the way, this can be deterimental -- and not just with subqueries. Consider the following expression:

select coalesce(case when rand(checksum(newid())) < 0.5 then 'a' end, 'b')

It can return a NULL value -- despite the COALESCE(), because the first expression is evaluated twice. For fun, you can run this query:

select v.n, coalesce(case when rand(checksum(newid())) < 0.5 then 'a' end, 'b')
from (values (1), (2), (3), (4), (5), (6), (7), (8)) v(n);

I can speculate on several reasons why SQL Server would behave this way.

(1) Someone at Microsoft or Sybase (once upon a time) actually thought this was the right approach.

(2) Someone thought "we already have a function that does this, so COALESCE() should be a little different". Even if that "little difference" makes it look like it is broken.

(3) SQL Server does not optimize subqueries by running them only once (as far as I can tell). So for subqueries in particular the idea might have been: "we'll fix this in a later round of optimization".

This is all speculation (and hence opinion). I wanted to answer because this does not only affect subqueries.

Upvotes: 1

Related Questions