>> What about changing the remaining inner join to left join
>> Select BaseTbl.RowID
>> from BaseTbl
>> left join Tbl_2 on Tbl2.Y = BaseTbl.Y
>> where BaseTbl.Col=?
>> and see if the SQLiter optimizer now leaves Tbl_2 out from the query
>> plan. It will only do that if it is not a 1-to-n join.
> If Tbl_2 isn’t involved in the columns, where or order by then
> changing it to left join will mean it will definitely be left out so
> I don’t get what you mean E.Pasma.
I’m talking bollocks again. The left join will only be omitted if it has a 1
to 1 relationship with BaseTbl so E.Pasma is correct.
I’m now wondering if you omit the WHERE & ORDER BY and run the following
EXPLAIN QUERY PLAN
left join Tbl1 on comparison_1
left join Tbl2 on comparison_2
left join Tbln on comparison_n
then if it returns more than 1 row then this implies there’s a 1 to many
relationship in the query and we can’t proceed. (At this stage I’m by no
means sure of this).
Otherwise the RowSQL becomes
jointype_1 Tbl1 on comparison_1
jointype_2 Tbl2 on comparison_2
jointype_n Tbln on comparison_n
ORDER BY ...
And we leave the SQLite optimiser to filter out any left joins that aren’t
Sent from: http://sqlite.1065341.n5.nabble.com/
sqlite-users mailing list