ShadowMarta at yahoo.de wrote: > ok, understand but I can't see why "NOT IN" would not force a full table scan > but "LEFT JOIN" would ?
In a query like "SELECT ... WHERE ID NOT IN (SELECT ...)", the subquery is executed _once_, the results are put into a temporary table/index, and the remaining query can be executed efficiently. (If the subquery were a correlated subquery, it would need to be executed multiple times, and the situation would be different.) > SELECT ART.* FROM ART JOIN (SELECT docid FROM OCR WHERE FullText MATCH > 'mailing') AS ftstable WHERE ART.ID = docid; This is correct, but more complex than needed: SELECT * FROM ART WHERE ID IN (SELECT docid FROM OCR WHERE FullText MATCH 'mailing') There is no need to try to force every query to use joins. Some databases are said to have optimizers that work much better with joins, but this is not true for SQLite. Regards, Clemens
