Posted by Gert-Jan Strik on 03/01/06 22:40

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

[Back to original message]