Thread: RE: [GENERAL] Simulating an outer join

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

************

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
>
> ************