From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Mike Mascari <mascarm(at)mascari(dot)com> |
Cc: | PostgreSQL-general <pgsql-general(at)postgreSQL(dot)org> |
Subject: | Re: [GENERAL] Simulating an outer join |
Date: | 2000-01-12 20:55:57 |
Message-ID: | 200001122055.PAA14434@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2000-01-12 21:00:40 | Re: [GENERAL] Simulating an outer join |
Previous Message | Oliver Mueschke | 2000-01-12 20:48:19 | Re: [GENERAL] constant column value in view with union |