Bob Dankert <[EMAIL PROTECTED]> wrote:
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?
Purely stylistic, readability reasons. See http://www.sqlite.org/optoverview.html - internally SQLite converts ON conditions into WHERE conditions.
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?
SQL query planner is not as simple-minded as you imagine it to be. SQL query is not a set of instructions to be executed one by one, left to right. The optimizer does a fairly sofisticated processing to come up with a good query execution plan.
Igor Tandetnik
