Post reply

"NULL" behaves wierd ...WHY ??????

  • December 31, 2009 at 12:27 am

    #213945

    create table #tmp

    ( id1 int, id2 int)

    insert into #tmp

    select null , null

    union

    select null , 1

    union

    select null, 2

    1) select count(*) , count(id1), count(id2), sum(id2)

    from #tmp

    drop table #tmp

    2) select 1 + null

    ---------------------------------------------------------

    In above first query , why count(*) and count (id1 ) result differently

    how NULL behaves in both the cases

    And why SUM(ID2) returns 3 instead of "NULL",while select 1 + nulll results in "NULL"

    IS "+" and Sum have different functionality

    or is it because of SP3 patch , which we had month ago.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 31, 2009 at 12:35 am

    #1097759

    NULL represents a value that is "Unknown" or "Not Known".

    If you add something to "Unknown" the result will be unknown so 1+NULL = NULL.

    In the case of SUM function, I believe it is designed to exclude NULLs. It is by design and has always worked like that.

    Similaryly, count() function also excludes null but when you count(*) it gives you the count of number of rows in the table.

    When you cound(id2), you are basically asking count of the number of rows where "id2" has a value. NULL is "Unknown" so it excludes the records where id2 is null.

    Lets say you add one more row and set id2 = 2.

    Count(id2) and count(distinct id2) will yeild different results. Hope that will clear things up a little.

  • December 31, 2009 at 12:42 am

    #1097762

    good answer.

    "Keep Trying"

  • December 31, 2009 at 4:30 am

    #1097824

    but why count(*) wll be treated differently from count(id1) though both contains same records?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 31, 2009 at 4:57 am

    #1097834

    Bhuvnesh (12/31/2009)

    but why count(*) will be treated differently from count(id1) though both contains same records?

    Not true.

    Count(*) will count all records in the table (Basically counting the internal id that identifies a row. There can't be any NULL values by definition.)

    Count(id) will count the NOT NULL values within that specific column.

    So, both Count() methods behave the same.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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