Re: why the need for is null?

Hi all;

Here is a brief guide to NULL's and Referential Integrity:

NULL is a special SQL value meaning 'unknown.'  Well, it is a little more
complicated and NULL can mean "value does not exist."  Therefore X = NULL is
NULL becuase we don't know if the NULL is equal to X.  So:
NULL does not equal NULL
NULL does not equal anything else.
NULL does not equal an empty string
You get the picture.

Think of it this way:  2 non-existant or unknown values don't equal any
other existant or non-existant value, known or unknown.

Now, referential integrity is defined as follows:
For every non-NULL foreign key, there is a corresponding primary key in the
referenced table.
Note that NULL's are specifically handled in the RI definition.

When do I use NULL's?  NULL's have a few uses:
1:  To indicate that the foreign key does NOT correspond with a primary key
in the referencing table.  In this case, NULL means something like "value
does not exist" (this is not the same as an empty value).  In an employee's
table, a NULL in the manager's field would mean "Employee does not report to
any other employee as a manager."
2:  To indicate that the value probably exists, but is unknown at present
(we don't know this customer's address, so we set it to NULL).

Best Wishes,
Chris Travers