RE: Question on SQL joining

Just getting caught up after the holiday weekend...

While those SQL statements are interchangeable for the given statement
which uses an INNER JOIN, you need to be careful about using this for an
OUTER JOIN. For an OUTER JOIN, it /does/ matter where the predicate goes.

Good explanation here:
https://blog.jooq.org/2019/04/09/the-difference-between-sqls-join-on-clause-and-the-where-clause/

For this reason, in general I recommend that local selection go in a WHERE
clause.

Thank you,

Tim Clark
DB2 for IBM i / SQL Optimizer


"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 11/20/2020
11:03:17 AM:

From: Dave <dfx1@xxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 11/20/2020 11:03 AM
Subject: [EXTERNAL] Re: Question on SQL joining
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

Hi Birgitta,
I nearly always use the first way of writing my joins as I find it much
more intuitive, it's just that I never saw anyone else do it or read it
Was wondering if there was something I missed

Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> schrieb am Do., 12. Nov. 2020,
09:54:

Yes!
In either way they should be ... or do you get any differences?

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"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

Dave

Sent: Donnerstag, 12. November 2020 09:49
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: Question on SQL joining

Hi,
Are these two statements effectively the same?

Select * from tab1 a join tab2 b
on a.col1 = b.col1 and a.mycol = 'xyz'

Select * from tab1 a join tab2 b
on a.col1 = b.col1
where a.mycol = 'xyz'

Thanks