Re: Difference between inner join and join in SQL query

Okay just a short explication of the different joins: (What I wrote in
parenthesis is just optional):

(Inner) Join: Only rows that are found in both tables/views are
returned

Left (Outer) Join: All rows from the first (left) table/view are
returned even if there is no match in the second table/view
If there is no equivalent in the second table,
NULL values will be returned

Right (Outer) Join: All rows from the second (right) table/view are
returned even if there is no match in the first table/view
If there is no equivalent in the second table,
NULL values will be returned

Full (Outer) Join: All rows of both tables are returned independent
if there is a match or not

(Left) Exception Join: All rows from the first (left) table/view are
returned where in the second (right) table/view no match is found

Right Exception Join: All rows from the second (right) table/view are
returned where in the first (left) table/view no match is found

Cross Join: All rows of the first table/view are connected to
all rows of the second table (cartesian product)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Javier Sanchez
Sent: Freitag, 15. Juli 2022 20:02
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Difference between inner join and join in SQL query

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.



El vie, 15 jul 2022 a las 11:41, jerry ven (<jerryven95@xxxxxxxxx>)
escribió:

Hi,

For the below statement :-

"They simply are not the same and would give different results
depending on what you want back. "


Does it refer with respect to 'INNER JOIN' and 'JOIN' ?

or does it refer to 'LEFT OUTER JOIN' and 'RIGHT OUTER JOIN ' here ?

Could you please clarify ?


Thanks.





On Fri, 15 Jul 2022 at 22:53, Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

LEFT OUTER JOIN and RIGHT OUTER JOIN are a different thing. The
set theory is the base of those constructs. You need to study set
theory.a little more. They simply are not the same and would give
different

results

depending on what you want back.

Javier.

El vie, 15 jul 2022 a las 11:18, Sam_L (<lennon_s_j@xxxxxxxxxxx>)
escribió:

Google 'inner join vs join'

On 7/15/2022 12:58 PM, jerry ven wrote:

Hi,

When I join two files based on ' INNER JOIN' or by simply using

'JOIN'

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

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

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

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

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