Re: [sqlite] Help with left joins

[x]

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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
R Smith <rsm...@rsweb.co.za>
Sent: Monday, November 20, 2017 3:55:42 PM
To: sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with left joins

[x]

>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] 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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Simon Slavin <slav...@bigfraud.org>
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 <tam118...@hotmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with left joins

[x]

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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Simon Slavin <slav...@bigfraud.org>
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 <tam118...@hotmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with left joins

[Simon Slavin]

On 20 Nov 2017, at 11:09am, x  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help with left joins

[x]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users