Re: [ EXTERNAL ] SQL Gurus ...

On Mar 12, 2021, at 1:22 PM, Ken Meade <kmeade@xxxxxxxxxxxxxxxxx> wrote:

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