Using <t1> JOIN <t2> ON <expr> is exactly the same as merely listing the tables and pushing the ON <expr> into the where clause. In fact, except for OUTER JOIN operations, the ON clause is always processed by "pushing" the conditions specified in the ON clause to the WHERE clause (or you may just think of it as yet another WHERE clause, multiple WHERE clauses being ANDed together in the final effect).
OUTER JOIN is different because the specified condition must be bound to the table to which it applies since the join is performed as both an equijoin and may also be ignored (as in result in a projected record where the join condition fails). LEFT or RIGHT specifies to which table in the recursive descent the condition must "not" be bound (that is, A LEFT JOIN B ON <expr>) means that <expr> must be bound on descent into table B (since no result on the join condition is a valid result). Binding the ON condition for LEFT and RIGHT OUTER joins is easy, though in theory the FULL OUTER JOIN ON must be bound on both tables since either descent may produce a valid null projection. A CROSS JOIN B is really a regular inner join and is processed as such, except that the nested loop is constrained such that B must always be an inner loop to A ... As such, except in OUTER joins, you do not even have to have the ON expression related to the table(s) which have been seen so far or even those in the join expression ... because ON is merely a syntactic substitute for WHERE and multiple WHERE clauses are really no more than AND conditions. The requirement for the table in an ON clause to only refer to tables already mentioned only applies to OUTER joins ... sqlite> create table a(a,b); sqlite> create table b(b,c); sqlite> create table c(c,d); sqlite> .eqp on sqlite> select a.a, a.b, c.c, c.d ...> from a join b on b.c=c.c ...> join c on a.b = b.b; QUERY PLAN |--SCAN TABLE a (~1048576 rows) |--SEARCH TABLE b USING AUTOMATIC COVERING INDEX (b=?) (~20 rows) `--SEARCH TABLE c USING AUTOMATIC COVERING INDEX (c=?) (~20 rows) sqlite> select a.a, a.b, c.c, c.d ...> from a,b,c ...> where a.b = b.b ...> and b.c = c.c; QUERY PLAN |--SCAN TABLE a (~1048576 rows) |--SEARCH TABLE b USING AUTOMATIC COVERING INDEX (b=?) (~20 rows) `--SEARCH TABLE c USING AUTOMATIC COVERING INDEX (c=?) (~20 rows) sqlite> select a.a,a.b,c.c,c.d ...> from a left join b on a.b=b.b and b.c=c.c ...> join c; Error: ON clause references tables to its right --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jose Isaias Cabrera >Sent: Wednesday, 27 February, 2019 07:42 >To: David Raymond; SQLite mailing list >Subject: Re: [sqlite] Getting data from two JOIN tables > > >Thanks, David. I actually like the comma (,) than the words (JOIN, >etc). Less wordy and, to me, more logically flow-y. > >josé > >From: sqlite-users on behalf of David Raymond >Sent: Wednesday, February 27, 2019 09:31 AM >To: SQLite mailing list >Subject: Re: [sqlite] Getting data from two JOIN tables > >I believe a comma is basically an inner join with no ON clause. > >I think this covers it >https://www.sqlite.org/lang_select.html#fromclause >"If the join-operator is "CROSS JOIN", "INNER JOIN", "JOIN" or a >comma (",") and there is no ON or USING clause, then the result of >the join is simply the cartesian product of the left and right-hand >datasets." > >So it's basically a personal preference. Similar to how it's been >noted that for inner joins, putting the constraint in the ON clause >or there WHERE clause results in the same meaning. > > >(For my personal preference the comma thing is one of those things >that drives me completely batty every time I see it. But that's just >the way my brain works when reading or creating a query) > > >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne >Sent: Wednesday, February 27, 2019 9:06 AM >To: SQLite mailing list >Subject: Re: [sqlite] Getting data from two JOIN tables > >On Wed, Feb 27, 2019 at 2:18 PM Jose Isaias Cabrera ><jic...@outlook.com> >wrote: > >> Thanks. This is exactly what I needed. So, there is really no >JOIN here, >> or is the "from t outer_t, z outer_z" a JOIN like statement? Where >can I >> read more about this? And yes, your assessment of t(a, idate) and >z(f, >> idate) being unique are true. >> > >Yes there is: > >select ... >> from t outer_t, z outer_z >> where a == f >> and a == 'p001' >> and ... >> > >That's equivalent to >select ... > from t outer_t > join z outer_z on t.a = z.f > >With additional where clauses involving correlated subqueries to >determine >the max(idate) on both sides, given a given a value (and thus f value >too, >given the join condition). > >At least that's how I read it. --DD >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
