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