Re: [ EXTERNAL ] Re: SQL Gurus ...

And Darren's example helped me understand a little better one of the

And Darren's example helped me understand a little better one of the other
threads from today.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Ken
Meade
Sent: Friday, March 12, 2021 1:23 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [ EXTERNAL ] Re: SQL Gurus ...

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Jon,

You were not alone in this way of thinking. Makes me wonder how many of
my past queries have been faulty.

Ken M.

----- Original Message -----
From: "Jon Paris" <jon.paris@xxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxxxxxxxx

Sent: Friday, March 12, 2021 1:15:30 PM
Subject: [ EXTERNAL ] Re: SQL Gurus ...

I meant it was against a column from the right table.

I understand now that for whatever reason SQL applies the where
effectively to an intermediate result set. That was not clear to me before
and everything I read about joins made a point of saying you always got all
rows from the left table. The bit that nobody mentioned was that this
produces an intermediate result set to which any where clause is then
applied. Not intuitive.

It is RPG brain think I know but my assumption was based on the way I
would code in RPG. i.e. read every record in the left table and chain to
the matching record in the right table applying the where clause to see if
it was considered a true match. As I say that is RPG think and a lesson I
must learn as I use SQL for more than just simple stuff.

On Mar 12, 2021, at 9:49 AM, Charles Wilt <charles.wilt@xxxxxxxxx>

wrote:

WHERE is always conceptually against the results of the join(s)..

Not sure where you read " the where clause was against the right table"

but

the source is either wrong or was mis-read.

Charles


On Fri, Mar 12, 2021 at 7:12 AM Jon Paris <jon.paris@xxxxxxxxxxxxxx>

wrote:

Thanks Peter - good explanation - and one that was sorely missing form

any

and all of the explanations of the left outer join that I could find.

I did think of it as a possibility but the docs all said all rows from

the

left table and the where clause was against the right table. I guess

the

join is done first and then the where causes the resulting set to be
filtered.


Jon

On Mar 11, 2021, at 8:01 PM, Peter Dow <petercdow@xxxxxxxxx> wrote:

Just for fun, imagine that every record in table1 has a record in

table2. But because you only want event = '2103SM', not all records are
selected. What's your record count?

You are selecting all records from table1 except those that have a

matching record in table2 with event not = '2103SM'. If you changed

your

WHERE to AND, it would do what you're expecting:

select count(*)
from table1 a left outer join table2 b
on a.email = b.email
and b.event = '2103SM'
order by attend;



On 3/11/2021 4:26 PM, Jon Paris wrote:

Can someone please explain how I can ever get a lower row count from a

left outer join than the number of rows that exist in the left table?

select count(*)
from table1 a left outer join table2 b
on a.email = b.email
where b.event = '2103SM'
order by attend;

That returns query returns a count of 848 rows. There are 858 rows in

table1! (left table). And just for fun an inner join also returns 848

rows.

Any ideas anyone? I thought it was an absolute that a left outer

always returned all rows in the left table.

Jon Paris

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