× 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.


LEFT JOIN is not an INNER JOIN and not the same as JOIN - according to the IBM i SQL Reference, there are 4 groups of JOIN operators - INNER, OUTER, EXCEPTION, and CROSS. The manual says - and Jerry, please do google searches or get the SQL Reference, all your answers are there.

The manual says that if there is no JOIN OPERATOR it is an INNER JOIN.

LEFT, RIGHT, or FULL by itself is an OUTER. You can't use OUTER by itself.

EXCEPTION by itself is a LEFT EXCEPTION, you can also specify RIGHT EXCEPTION.

Finally you have a CROSS.

These are very much SQL standard - IBM i is one of the most standards-compliant version of SQL in the industry.


On 7/15/2022 1:01 PM, Javier Sanchez wrote:
Okay, this is it. JOIN and INNER JOIN are just the same. Don't know why
the SQL standards took that for granted. LEFT JOIN also would work just
fine as well as JOIN as long as you know what you are doing. It's
confusing, isn't it. Okay, let's get back. INNER JOIN is the same as
JOIN. LEFT JOIN would also be the same thing as JOIN. But putting the
OUTER JOIN construct would give you different results depending on what
you want to do.

Suppose you have two tables A, and B. Now, if you select ALL records in
table A that correspond to the same rows as in table B related with some
key columns, that is called a cartesian relationship.
One to one relationship would give you all records in table A that have the
same key values as in table B. JOIN or LEFT JOIN or simply JOIN would
work well.

Now, here's the difference. Case 1. We want back all rows in table A and
also all those in table B that have the same key columns in the ON xxx =
yyy part of the SQL statement, but want also to include those rows in table
B that DO NOT match the key criteria. Those rows will be returned to you
with NULL values. So you'll know they are not in table B.

Case 2. RIGHT OUTER JOIN works in the opposite way. You will get all rows
in table B as well as in table A that coincide with the key columns but
you'll also get NULL rows in table A that ARE NOT in table B. So you will
know what is missing in table A that is not in table B.

Got it?


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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

This mailing list archive is Copyright 1997-2023 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.