Re: Query plan for NOT IN

On Mon, Oct 5, 2009 at 8:35 PM, Guy Rouillier <guyr-ml1(at)burntmail(dot)com>wrote:

> Grzegorz Jaśkiewicz wrote:
>
>>
>> well, as a rule of thumb - unless you can't think of a default value of
>> column - don't use nulls. So using nulls as default 'idunno' - is a bad
>> practice, but everybody's opinion on that differ.
>>
>
> I don't understand this point of view. The concept of null was introduced
> into the SQL vernacular by Codd and Date expressly to represent unknown
> values.
>
> Yes, unknown. So as long as you know the default value of field, you should
set it to such.

For instance, if by default your account balance is 0, you should set it to
0, not leave it as null, etc. Other example, if client doesn't have
description - leave it as blank '' string, instead of null.

On the other hand, if you want to denote that the value wasn't set - use
null, but use it wisely. Hence, I personally think that DEFAULT value (in
create table) should be compulsory, and 'DEFAULT NULL' an option, that you
would have to choose.

Not to mention other (valid in this case) argument, that you would mostly
use IN/EXISTS, and/or join keys on fields that are either PK, or at least
NOT NULL. Hence, using JOIN instead of IN/EXISTS most of the times.
One of My few personal wishes, ever since I started to use postgresql - is
that it could rewrite IN/EXISTS into JOIN - when possible (that is, when
columns are NOT NULL).

--
GJ