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);

Yes, this is our brain-damaged parser/optmizer that likes the usually
slower EXISTS with correlated subquery to the much clearer NOT IN.
Bummer.

I want to avoid having to put this workaround into my book, but I may
have no choice.  The work around is so non-obvious as to be a terrible
hinderance for normal users.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026