>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 

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

Reply via email to