Re: [GENERAL] Simulating an outer join

I was originally trying to avoid this, but I think you make a good point.
The default value is probably best for this case. Thanks for the solid
argument.


----- Original Message -----
From: "Culberson, Philip" <philip.culberson@dat.com>
> It seems to me that in this case Bruce would be better off to use a
default
> value and NOT "simulate" an outer join.
>
> I suggest the following:
>
> Instead of using a character abbreviation for the relation, use a number.
> Since the list of categories is most likely going to remain small, you can
> use an int2.  This has two advantages.
>
> 1) It is then truly divorced from the text description.  If you ever
change
> "Small Business" to "Not Really Big Business", the abbreviation "SB" loses
> it's meaning.
>
> 2) Less storage.  Per the user documentation, an int2 takes 2 bytes of
> storage.  Both char[n] and varchar[n] take 4+n bytes of storage, so even
if
> com_cat_abbr is NULL, you still burn at least 4 bytes!
>
> Default the value of com_cat_abbr to 0 and make an appropriate entry in
the
> company_category table (say, with a com_cat_long value of "Undefined").
>
> Since you are already using the lookup table to populate pulldowns,
> enforcing that the user makes a choice in your client app should not be a
> problem.
>
> Now you can just do a straight join and not incur the cost of doing a
union
> or sub-selects, etc.
>
> Hope this helps.
>
> Phil Culberson
>
> -----Original Message-----
> From: Mike Mascari [mailto:mascarm@mascari.com]
> Sent: Wednesday, January 12, 2000 9:47 AM
> To: Bruce Momjian
> Cc: PostgreSQL-general
> Subject: Re: [GENERAL] Simulating an outer join
>
>
> Bruce Momjian wrote:
> >
> > I have been thinking about how to simulate an outer join.  It seems the
> > best way is to do:
> >
> >         SELECT tab1.col1, tab2.col3
> >         FROM tab1, tab2
> >         WHERE tab1.col1 = tab2.col2
> >         UNION ALL
> >         SELECT tab1.col1, NULL
> >         FROM tab1
> >         WHERE tab1.col1 NOT IN (SELECT tab2.col2 FROM tab2)
> >
> > Comments?  I know someone was asking about this recently.
> >
>
> I wouldn't use IN ;-)
>
> SELECT table1.key, table2.value
> FROM table1, table2
> WHERE table1.key = table2.key
> UNION ALL
> SELECT table1.key, NULL
> FROM table1 WHERE NOT EXISTS
> (SELECT table2.key FROM table2 WHERE table1.key = table2.key);
>
> Mike Mascari
>
> ************