× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.