Hello, I like to post this remark again as it seems closely related to "Query flattening for left joins involving subqueries on the right- hand side".
I have a complete different reason though. For playing with sudoku solving, I have a table representing the digits 1..9: CREATE TABLE digit (z integer primary key, i integer); Besides I have an entity part for representing squares on a line and this has excatly the same content as digit. For the sake of economy I made it a view: CREATE VIEW part AS SELECT z AS p, i FROM digit AS part; This executes just as fast as a table and the addition "AS part" conveniently appears in query plan explanations. It is just the outer join that is treated differently: CREATE TABLE t (x); EXPLAIN QUERY PLAN SELECT * FROM t LEFT OUTER JOIN digit ON z=x; 0|0|0|SCAN TABLE t 0|1|1|SEARCH TABLE digit USING INTEGER PRIMARY KEY (rowid=?) EXPLAIN QUERY PLAN SELECT * FROM t LEFT OUTER JOIN part ON q=x; 1|0|0|SCAN TABLE digit AS part 0|0|0|SCAN TABLE t 0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (p=?) This is indeed well documented in rule 3 of <http://www.sqlite.org/optoverview.html#flattening >. I also wonder if this rule could be relaxed. May be a query that does not include any of the special cases mentioned in the document, might be tolerated. Or may be for a casual reader this things look easier than they are. Thanks, E. Pasma
