RE: Difference between inner join and join in SQL query

Not all SQL versions have EXCEPTION JOIN (MySQL is one of them) so I have changed my approach to use "where not exists" clauses so it is version independent.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg via MIDRANGE-L
Sent: Saturday, July 16, 2022 8:53 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: Difference between inner join and join in SQL query

​​​​​Javier

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.

Cheers
Vern

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?

Javier.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com