Loading ...
Sorry, an error occurred while loading the content.
 

ZERO vs NULL

Expand Messages
  • Dereck L. Dietz
    From the standpoint of an Oracle programmer/dba there is a difference between a 0 and a NULL. A zero is a value. It s value is ZERO. A null by definition is
    Message 1 of 3 , May 2, 2008
      From the standpoint of an Oracle programmer/dba there is a difference between a 0 and a NULL.
       
      A zero is a value.  It's value is ZERO.
       
      A null by definition is an ABSENCE of a value.
       
      If you're not careful with the above distinctions you could default a value to 0 when it should actually remain a NULL and by doing so throw off statistics.
       
      For example a table with 10 rows of values:
       
      In the first - 5 values defaulted to 0 and 5 values with actual values.  The average of the values in table is 1.5
       
      0
      0
      0
      0
      0
      1
      2
      3
      4
      5
       
      In the second table - 5 values NOT defaulted to 0 and the same 5 actual values as the first.  The average of the values in this table is 3.
       
      NULL
      NULL
      NULL
      NULL
      NULL
      1
      2
      3
      4
      5
       
      The difference is in the first table the average function AVG() is using ALL the values even the 0 values.  In the second the NULL values don't exist so the AVG() function is only using the 5 values it knows about.
    • Tom Stillman
      This doesn t apply to just Oracle. It is a consideration in other databases I understand. Tom Stillman ...
      Message 2 of 3 , May 5, 2008
        This doesn't apply to just Oracle. It is a
        consideration in other databases I understand.

        Tom Stillman

        --- "Dereck L. Dietz" <dietzdl@...> wrote:

        > From the standpoint of an Oracle programmer/dba
        > there is a difference between a 0 and a NULL.
        >
        > A zero is a value. It's value is ZERO.
        >
        > A null by definition is an ABSENCE of a value.
        >
        > If you're not careful with the above distinctions
        > you could default a value to 0 when it should
        > actually remain a NULL and by doing so throw off
        > statistics.
        >
        > For example a table with 10 rows of values:
        >
        > In the first - 5 values defaulted to 0 and 5 values
        > with actual values. The average of the values in
        > table is 1.5
        >
        > 0
        > 0
        > 0
        > 0
        > 0
        > 1
        > 2
        > 3
        > 4
        > 5
        >
        > In the second table - 5 values NOT defaulted to 0
        > and the same 5 actual values as the first. The
        > average of the values in this table is 3.
        >
        > NULL
        > NULL
        > NULL
        > NULL
        > NULL
        > 1
        > 2
        > 3
        > 4
        > 5
        >
        > The difference is in the first table the average
        > function AVG() is using ALL the values even the 0
        > values. In the second the NULL values don't exist
        > so the AVG() function is only using the 5 values it
        > knows about.



        ____________________________________________________________________________________
        Be a better friend, newshound, and
        know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
      • Tangotiger
        ... This is consistent with how we are handling it. If you have an example contrary to this, please let us know. Tom
        Message 3 of 3 , May 6, 2008
          > A zero is a value. It's value is ZERO.
          >
          > A null by definition is an ABSENCE of a value.
          >

          This is consistent with how we are handling it. If you have an example
          contrary to this, please let us know.

          Tom
        Your message has been successfully submitted and would be delivered to recipients shortly.