Help with left joins

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with left joins

curmudgeon
Suppose

TblB has primary key ColB and contains a column ColBX
TblC has primary key ColC and contains a column ColCX
TblD has primary key ColD and contains a column ColDX

TblA has primary key ColA and also contains columns ColB, ColC and ColD

(i.e. TblB, TblC and TblD are effectively lookup tables for TblA).

If I run the following query

explain
select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColBX=?;

there will be no trace of TblC or TblD as they're redundant.

If i run the following query

explain
select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;

there will be no trace of TblD as it's redundant but what I need explaining is why is TblB included in the explain.

I'm guessing that the sqlite query optimiser finds it easy to ignore trailing redundant tables but checking for other redundant tables is too expensive. Is that the case or am I missing something and the inclusion of TblB is necessary to get the correct result?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with left joins

Simon Slavin-3


On 20 Nov 2017, at 11:09am, x <[hidden email]> wrote:

> explain
> select ColA from TblA
> left join TblB using (ColB)
> left join TblC using (ColC)
> left join TblD using (ColD)
> where ColBX=?;
>
> there will be no trace of TblC or TblD as they're redundant.

Although you have not asked for columns from the table TblC, you have specified the lookup.  Perhaps you want to check that an appropriate row appears in TblC, and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see that a JOIN is needed before it does it.  But at the moment this command is just a little slower than it should be, not exactly wrong.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with left joins

curmudgeon
I’m not sure what you’re saying Simon or maybe you’re not sure what I’m asking. I’m not complaining about the fact SQLite fails to drop what I think is a redundant table in the second explain, I’m merely wanting to check that it is a redundant table in case I’ve got a wrong idea about left joins. Put it this way will



select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;



return the same result set as



select ColA from TblA
left join TblC using (ColC)
where ColCX=?;



even though the explains are different?



________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Monday, November 20, 2017 12:34:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with left joins



On 20 Nov 2017, at 11:09am, x <[hidden email]> wrote:

> explain
> select ColA from TblA
> left join TblB using (ColB)
> left join TblC using (ColC)
> left join TblD using (ColD)
> where ColBX=?;
>
> there will be no trace of TblC or TblD as they're redundant.

Although you have not asked for columns from the table TblC, you have specified the lookup.  Perhaps you want to check that an appropriate row appears in TblC, and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see that a JOIN is needed before it does it.  But at the moment this command is just a little slower than it should be, not exactly wrong.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with left joins

David Raymond
Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has the potential of returning multiple rows, which will carry over to the next joins.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of x
Sent: Monday, November 20, 2017 8:22 AM
To: SQLite mailing list
Subject: Re: [sqlite] Help with left joins

I’m not sure what you’re saying Simon or maybe you’re not sure what I’m asking. I’m not complaining about the fact SQLite fails to drop what I think is a redundant table in the second explain, I’m merely wanting to check that it is a redundant table in case I’ve got a wrong idea about left joins. Put it this way will



select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;



return the same result set as



select ColA from TblA
left join TblC using (ColC)
where ColCX=?;



even though the explains are different?



________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Monday, November 20, 2017 12:34:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help with left joins



On 20 Nov 2017, at 11:09am, x <[hidden email]> wrote:

> explain
> select ColA from TblA
> left join TblB using (ColB)
> left join TblC using (ColC)
> left join TblD using (ColD)
> where ColBX=?;
>
> there will be no trace of TblC or TblD as they're redundant.

Although you have not asked for columns from the table TblC, you have specified the lookup.  Perhaps you want to check that an appropriate row appears in TblC, and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see that a JOIN is needed before it does it.  But at the moment this command is just a little slower than it should be, not exactly wrong.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with left joins

curmudgeon
>Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has the potential of returning multiple rows, which will carry over to the next joins.

Thanks David, I did say ColB, ColC & ColD were primary keys.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with left joins

R Smith

On 2017/11/20 5:33 PM, x wrote:
>> Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has the potential of returning multiple rows, which will carry over to the next joins.
> Thanks David, I did say ColB, ColC & ColD were primary keys.

Any kind of Query (especially of the SELECT variety) can have multiple
redundancies or  indeed omit-able specified values. An optimization to 
ensure non-used left-joins on Primary keys are not used is probably a
negligible improvement.

To answer your question: You are correct to assume the same output -
There is no difference in the SET-Theory/Algebra producing those
specific rows whether you omit the left joins or not, BUT that only
holds true while the Indexes are indeed all PRIMARY (or at least Unique)
and the joins are of the "LEFT" variety. Change any of those and the
query output may look very different.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with left joins

curmudgeon
Thanks Ryan. When I saw the redundant table still in the explain I was worried I had misunderstood something about left joins.



Tom



________________________________
From: sqlite-users <[hidden email]> on behalf of R Smith <[hidden email]>
Sent: Monday, November 20, 2017 3:55:42 PM
To: [hidden email]
Subject: Re: [sqlite] Help with left joins


On 2017/11/20 5:33 PM, x wrote:
>> Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has the potential of returning multiple rows, which will carry over to the next joins.
> Thanks David, I did say ColB, ColC & ColD were primary keys.

Any kind of Query (especially of the SELECT variety) can have multiple
redundancies or  indeed omit-able specified values. An optimization to
ensure non-used left-joins on Primary keys are not used is probably a
negligible improvement.

To answer your question: You are correct to assume the same output -
There is no difference in the SET-Theory/Algebra producing those
specific rows whether you omit the left joins or not, BUT that only
holds true while the Indexes are indeed all PRIMARY (or at least Unique)
and the joins are of the "LEFT" variety. Change any of those and the
query output may look very different.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users