>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’ve now realised what caused my earlier confusion regarding inner joins. I was getting mixed up with foreign keys which do offer a way of finding redundant inner joins. If a Tbl (which is not involved in the query columns, where or order by) is inner joined to BaseTbl on all the columns of a unique index and it turns out there is a foreign key matching that join then the Tbl can be left out if the BaseTbl columns are defined as NOT NULL. If they are allowed to be NULL then it can still be left out provided the NOT NULL condition(s) is added to the where. Using the earlier example Select BaseTbl.RowID from BaseTbl left join Tbl_1 on Tbl_1.X = BaseTbl.X Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y left join Tbl_3 on Tbl3.Z = BaseTbl.Z where BaseTbl.Col=? If BaseTbl has FOREIGN KEY(Y) REFERENCES Tbl_2(Y) then If BaseTbl.Y is defined as NOT NULL the query can be reduced to Select BaseTbl.RowID from BaseTbl where BaseTbl.Col=? If BaseTbl.Y allows NULLs then the query can be reduced to Select BaseTbl.RowID from BaseTbl where BaseTbl.Col=? and BaseTbl.Y IS NOT NULL Could someone confirm I’ve got the above right? If I’m correct, I wonder if the optimiser takes (or could take) this into account. As lookup tables are probably the main use of foreign keys you’d think there’d be no shortage of such joins. That said, I suppose if you’re aware of what you want the optimiser to do it wouldn’t offer any advantage over left joining and adding the appropriate NOT NULL constraint(s) to the WHERE. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
