Re: [GENERAL] Simulating an outer join

2000-01-13 Thread Bruce Momjian
> Bruce Momjian wrote: > > > I am just writing the EXISTS section from my book. I don't think it > > matters what fields are returned from an EXISTS subquery. If I am > > wrong, someone please let me know. > > Celko also writes (in his chapter on EXISTS in "SQL for Smarties"): > > "In general

Re: [GENERAL] Simulating an outer join

2000-01-13 Thread Julian Scarfe
Bruce Momjian wrote: > I am just writing the EXISTS section from my book. I don't think it > matters what fields are returned from an EXISTS subquery. If I am > wrong, someone please let me know. Celko also writes (in his chapter on EXISTS in "SQL for Smarties"): "In general the SELECT * opti

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Bruce Momjian
> > UNION ALL > > SELECT table1.key, NULL > > FROM table1 WHERE NOT EXISTS > > (SELECT table2.key FROM table2 WHERE table1.key = table2.key); > > FWIW, that's exactly Joe Celko's SQL-89 workaround for OUTER JOINs in 'SQL for > Smarties'. Well in fact he uses (SELECT * FROM table2 WHERE table1.key

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Julian Scarfe
> 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 > >

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Bruce Momjian
> Can somebody comment on using EXISTS vs. IN in a subselect? I have > some statements with subselects, and I'd like to understand the > ramifications of choosing EXISTS or IN. We have some brain-damaged code that is faster with EXISTS than IN. With IN, the subquery is evaluated and the result

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Bruce Momjian
> 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 >

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Bruce Bantos
union > or sub-selects, etc. > > Hope this helps. > > Phil Culberson > > -Original Message- > From: Mike Mascari [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 12, 2000 9:47 AM > To: Bruce Momjian > Cc: PostgreSQL-general > Subject: Re: [GENERAL] Simul

RE: [GENERAL] Simulating an outer join

2000-01-12 Thread Culberson, Philip
Original Message- From: Mike Mascari [mailto:[EMAIL PROTECTED]] 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

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Sarah Officer
Can somebody comment on using EXISTS vs. IN in a subselect? I have some statements with subselects, and I'd like to understand the ramifications of choosing EXISTS or IN. Sarah Officer [EMAIL PROTECTED] Mike Mascari wrote: > > Bruce Momjian wrote: > > > > I have been thinking about how to simu

Re: [GENERAL] Simulating an outer join

2000-01-12 Thread Mike Mascari
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 >

[GENERAL] Simulating an outer join

2000-01-12 Thread Bruce Momjian
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