Re: [GENERAL] Simulating an outer join

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
officers(at)aries(dot)tucson(dot)saic(dot)com

Mike Mascari wrote:
>
> 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
>
> ************