On Friday, 28 April, 2017 15:55, Simon Slavin <slav...@bigfraud.org> wrote:
> > The only difference is the explicit JOIN statement. I was under the > > impression that using this, vs. the way I wrote it, is a matter of taste > > that doesn’t affect the execution of the query. > SQLite computes two-table searches using nested loops. Providing the JOIN > you want tells SQLite which order you think the loops should be nested in. > Expressing the searches with JOINs is also helps me figure out what SQLite > is actually doing. Before I did that it wasn’t obvious to me how little > the table "docs" mattered for this query. NO IT DOES NOT. FROM <table1> JOIN <<table2> ON <expression> IS EXACTLY THE SAME AS FROM <table1>, <table2> WHERE <expression> THERE IS NOT DIFFERENCE WHATSOEVER. EVER. PERIOD. It is also exactly the same as FROM <table1> JOIN <table2> WHERE <expression> FROM <table2> JOIN <table1> WHERE <expression> FROM <table2> JOIN <table1> ON <expression> FROM <table2>, <table1> WHERE <expression> The *ONLY TIME EVER THAT "ON <expression>" is not IDENTICAL to leaving out the JOIN keyword and putting the <expression> in the WHERE clause is the case of an OUTER (LEFT) JOIN. In all other cases they are just different spellings of exactly the same thing. The other somewhat limited exception is the CROSS JOIN which specifies the visitation order where the LHS table is the outer loop and the RHS table is the inner loop, however, in that case the ON clause is identical to a WHERE clause and is nothing more than alternate spelling (just like we spell colour properly here, but the Yangs spell it color). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
