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
