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