Re: Why are empty VARCHAR2S always NULL?

> "NULL" is not "I don't know" but rather "I know that I don't know" :)
> It's like a NULL pointer in C in some respect (except for the ternary
> logic).

To me, NULL is more like "no value". This makes everything perfectly clear and sensible. Since there is no value, binary comparisons do not apply (you can't find out if it's equal or not equal to something, including another no-value).

> > As of your particular case, if your part is not yet shipped, it is
> > unknown to whom it was shipped.
>
> No: It was shipped to nobody, and that's a known fact. The table design
> says that an empty customer ID means "no customer", i. e. not shipped.
> A NULL customer ID means "unknown". That's a big difference, but perhaps
> one has to study Zen in order to accept it.

Hmm... So following your logic, unshipped part is shipped. Just to nobody in particular. To me, part with no customer id is simply not ordered and not shipped - it's just lying around the warehouse waiting its turn. As soon as customer places an order and you assign certain part to this order, customer id is known, as well as shipment status, which is another attribute of part entity, and the status is 'not shipped' or something similar. This status attribute has no functional dependency on customer id, so you can't use customer id value to determine the shipment status - if part is not ordered, its status is certainly known ('not shipped' or whatever you prefer), while customer id is not known because no customer ordered it yet. Looks like your E/R model is wrong...

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.