I have been pondering an issue for a while regarding the separation of query conditions from the join condition and the where condition of the query. All I have been able to find on this matter is general text "use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set", but I have not been able to get any reason why this is? Take the following table:
CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE table2 (id INTEGER PRIMARY KEY, table1id INTEGER, name TEXT); CREATE INDEX table2index ON table2(table1id); And I do the following query: SELECT table2.name FROM table1 JOIN table2 ON table2.table1id = table1.id WHERE table1.name like 'bob%'; Wouldn't it be better to put the filter on table1.name in the on condition as well so it does not have to join as many rows? If this is the case, it seems it would make sense to put almost all filtering conditions in the join condition rather than the where condition. I know this is not correct and I suspect it is related to the use of indexes while joining the tables. If this is the case, would it be quicker if I had an index on both table1id and the name columns from table2? I am more or less looking at the theory behind these optimizations in SQL and not a specific case for SQLite - just trying to understand how all this stuff works so I can write better queries. Thanks! Bob Dankert
