> Naive changes to support the two optimizations above fix most of these examples. But there are a couple of tricks. For example, query 1753 features the equivalent of: > > CREATE TABLE t1(a, b); > CREATE TABLE t2(x, y NOT NULL); > SELECT * FROM t1 LEFT JOIN t2 WHERE t1.a=t2.x AND y IS NULL; > > In the query, the LEFT JOIN can be transformed to a regular join (because t1.a=t2.x implies that t2.x cannot be NULL) which allows the database to determine that "y IS NULL" will always be false. But currently, by the time SQLite realizes it can transform the join it's tricky to tell exactly which LEFT JOIN (there may be many in the query) column "y" came from. I would say that this case (and the one following) are even simpler. There is no ON condition attached to the RHS table of the left join which means that the join type cannot be an outer join and must be an inner join and this can be done even before examination of the where clause.