Bob Dankert 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? 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
Bob,
There is a sample chapter from the O'Reilly book "The Art of SQL" at
http://www.oreilly.com/catalog/artofsql/chapter/index.html (click the
maneuvering link). This chapter discuss the various ways you can slice
and dice an SQL query and how it affects performance in a database
neutral way.
HTH
Dennis Cote
