want to know Difference between isnull(sum(xx),0), sum(isnull(xx,0))

  • Please find the following example.

    I want to know the difference between sum(ISNULL(i,0)), ISNULL(sum(i),0)

    Both will return the same value.

    Which one will be correct and also tell me about the query cost.

    declare @t as table (i numeric(5,0),j int)

    insert into @t

    values (null,null),(NULL,1),(NULL,NULL),(NULL,3)

    select * from @t

    select sum(i),sum(j) from @t

    select sum(ISNULL(i,0)),sum(ISNULL(j,0)) from @t

    select ISNULL(sum(i),0),ISNULL(sum(j),0) from @t

    Thanks in advance 🙂

  • The SUM function ignores NULL values.

    But, a regular sum also ignores 0 values (x + 0 = x).

    So both expressions are equal. sum(ISNULL(i,0)) has a few operations more than the other one, as ISNULL is called for every row.

    But with the CPUs nowadays, this difference is negligible. Besides, the query optimizer might maybe change the order of the functions to optimize the query.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • One extra point, although the expressions will usually return the same value, there is a difference if there are no rows in the table.

    The first expression would return NULL but the second expression would return 0.

    _______________________________________________________________

    Website : www.sqlmatters.com

  • Hi All,

    I find the difference.

    declare @t as table (i numeric(5,0),j int)

    insert into @t

    values (null,null),(NULL,1),(NULL,NULL),(NULL,3)

    select * from @t

    select sum(i),sum(j) from @t

    select sum(ISNULL(i,0)),sum(ISNULL(j,0)) from @t

    select ISNULL(sum(i),0),ISNULL(sum(j),0) from @t

    If table has no records the following query will return NULL

    select sum(ISNULL(i,0)),sum(ISNULL(j,0)) from @t

    the following query will return 0

    select ISNULL(sum(i),0),ISNULL(sum(j),0) from @t

    Thanks to all.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply