My question is, is there any difference between a NULL and a Blank
(Unknown, Not Applicable) field in MS SQL or are they the same?
Awaiting your comments,
RegardsAm 1 Mar 2006 01:57:11 -0800 schrieb Shwetabh:
> Hi,
> My question is, is there any difference between a NULL and a Blank
> (Unknown, Not Applicable) field in MS SQL or are they the same?
> Awaiting your comments,
> Regards
Yes, a very big difference! Be carefully if you have NULL valued fields. If
you do a compare and one or both are NULL, then the result is always NULL,
never true or false. Even comparing two fields which are both NULL will
give NULL as result, not true! Or if you have something like "select
sum(field) from ..." and one or more are NULL, then the result will be
NULL. Use always "if field is NULL ..." for NULL checking and for safety
maybe something like "select sum( IsNull(field,0) ) from ...". Check the
function ISNULL() in the manual.
bye,
Helmut|||I thought aggreators like SUM ignored nulls...|||Please see some corrections inline...
helmut woess wrote:
> Am 1 Mar 2006 01:57:11 -0800 schrieb Shwetabh:
> > Hi,
> > My question is, is there any difference between a NULL and a Blank
> > (Unknown, Not Applicable) field in MS SQL or are they the same?
> > Awaiting your comments,
> > Regards
> Yes, a very big difference! Be carefully if you have NULL valued fields. If
> you do a compare and one or both are NULL, then the result is always NULL,
> never true or false.
No, the comparison "<somevalue> = NULL" will result in UNKNOWN. If the
predicate is part of the WHERE clause, then the row is removed from the
result. If the predicate is part of a CHECK constraint, then the row is
allowed.
> Even comparing two fields which are both NULL will
> give NULL as result, not true!
The result of the comparison "NULL = NULL" also results in UNKNOWN.
> Or if you have something like "select
> sum(field) from ..." and one or more are NULL, then the result will be
> NULL.
NULL values are excluded from aggregates. If one or more NULL values are
encountered, SQL Server will issue a warning stating that these rows are
disregarded. The only exception is the aggregate COUNT(*)
> Use always "if field is NULL ..." for NULL checking and for safety
> maybe something like "select sum( IsNull(field,0) ) from ...".
This only good advice if you want a NULL row to be treated as 0 in an
aggregation (for example the calculation of an average).
> Check the function ISNULL() in the manual.
> bye,
> Helmut
In addition to Helmut's warnings, note that NULLs are promoted in
expressions. So if you write SELECT A + B AS sum_of_A_and_B and either A
or B is NULL, then sum_of_A_and_B will be NULL.
HTH,
Gert-Jan|||Shwetabh (shwetabhgoel@.gmail.com) writes:
> My question is, is there any difference between a NULL and a Blank
> (Unknown, Not Applicable) field in MS SQL or are they the same?
As said in other posts, they are not. I just like to add one thing:
NULL stands for "unknown, not appliable". An empty string, is very much
a defined value as far as SQL is concerned.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
