Re: Matching Records

From: Brian Johnson

AKA full outer join. Not yet directly supported on System i, but IBM
provides an example for simulating one...

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbaf
ymst124.htm

Thanks Brian! This made me do some more research, and I found that in
addition to the FULL OUTER JOIN (which as you indicate is exactly what we're
looking for), there are also a set of RIGHT JOIN functions, which basically
reverse the selection process.

A RIGHT EXCEPTION JOIN returns records that only exist in the right file,
while RIGHT OUTER JOIN includes those records along with records that exist
in both. This exactly mirrors the LEFT EXCEPTION JOIN AND LEFT OUTER JOIN.

For some databases, LEFT JOIN is a synonym for LEFT OUTER JOIN, while RIGHT
JOIN is synonymous with RIGHT OUTER JOIN. Note that there is no LEFT INNER
JOIN (or RIGHT INNER JOIN) because an INNER JOIN includes only records that
are contained in both files, so the RIGHT vs. LEFT distinction makes no
difference in record selection.

As Brian pointed out, FULL OUTER JOIN returns all records, whether they
exist in both files or not. Interestingly, there is no FULL EXCEPTION JOIN,
which would return records that only exist in one file or the other, but not
both (but it's easy to simulate with a UNION of LEFT EXCEPTION and RIGHT
EXCEPTION).

Finally, various databases support some or all of these requests. Oracle
supports pretty much everything, although I don't think it likes the short
synonyms. You need to consult the documentation for your database to
determine which functions are supported.

Joe

P.S. I won't tell you about CROSS JOIN. You don't want to know.