>What about changing the remaining inner join to left join
>left join Tbl_2 on Tbl2.Y = BaseTbl.Y
>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
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
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
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
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
If BaseTbl.Y allows NULLs then the query can be reduced to
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