-
What is the best way of doing a full outer join between two tables ? That is, a query which gives you all the matching rows in A and B, all the rows in A but not B and all the rows in B but not A.
Obviously you can't do:
select a.*, b.*
from a, b
where a.key (+) = b.key (+)
Is the only option to do it as two separate queries i.e. something like
select a.*, b.*
from a, b
where a.key = b.key(+)
union
select a.*, b.*
from a, b
where a.key(+) = b.key
Or is there a neater way ?
-
Outer join is not supported by all RDBMS. Of course, Oracle supports one way outer join.
The other way of working is using NOT EXISTS.
Ex for one way outer join:
SELECT A.COL1, B.COL1
FROM A ,B
WHERE A.COL1 = B.COL1
UNION
SELECT A.COL1, ' '
FROM A
WHERE NOT EXISTS
(SELECT B.COL1 FROM B
WHERE A.COL1 = B.COL1);
-
...or wait for 9i :)
- Chris