[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Query plan gone haywire lays waste to my library's performance
From:       "Keith Medcalf" <kmedcalf () dessus ! com>
Date:       2017-04-28 23:40:12
Message-ID: 4f26a4414d2deb4695d76ba61db6423c () mail ! dessus ! com
[Download RAW message or body]

On Friday, 28 April, 2017 15:55, Simon Slavin <slavins@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


[prev in list] [next in list] [prev in thread] [next in thread]