On Tuesday, 3 September, 2019 07:19, dboland9 <dbola...@protonmail.com> wrote:
>Thanks for the info. As I understand things, this is an implicit join. The syntax for a SELECT statement goes like this: SELECT <the list of what data I want> FROM <the list of tables from whence it comes> WHERE <the condition that defines the rows I want> GROUP BY <the list things to group the rows I want> HAVING <the condition to select the groups I want> ORDER BY <the list of things to sort the results by I want> >I did try it, and it works just fine. However, in my reading it appears that >implicit joins are discouraged, and will only do left joins. Implicit joins (whatever that means) are not discouraged. And the "," in the list of tables may be replaced by the word JOIN. It is merely an alternate spelling. And the word ON is merely an alternate spelling of AND (plus some parenthesis -- you put parenthesis around the existing WHERE clause, put parenthesis around the ON clause, change the word ON to AND, and tack the result to the end of the (now parenthesized) WHERE clause). Using "commas" instead of "JOIN" there is no way to specify a particular join type, so "," always means "INNER JOIN" -- so if you need to specify a JOIN type other than an plain projection (inner) you must use the table "<join type> table" specification. Except for OUTER JOIN operations, the ON clause is merely a WHERE condition (in outer joins the ON clause binds the table that is being outer joined). In the olden days one used the special operator *= or =* or *=* to indicate outer joins in the where clause). The ON clause does not even need to contain references to tables that have already been mentioned since they are merely syntactic sugar. You can even have one without using the word JOIN at all as in "SELECT a,b,c FROM x,y,z ON x.a = y.b and y.g = z.c WHERE z.c = 5 or x.a = 3" which translates to "SELECT a,b,c FROM x,y,z WHERE (z.c = 5 or x.a = 3) and x.a = y.b and y.g = z.c" You will notice that all the things in the SELECT statement are defined as "I want". This is what makes SQL a declarative language -- you declare what you want, and it figures out how to go get it. >Also, it looks like you are using aliasing to shorten the query strings - >true or false? Yes. The tables are aliased and the as keyword is omitted. >‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ >On Tuesday, September 3, 2019 7:32 AM, John G <rjkgilles...@gmail.com> >wrote: > >> Or without the added calories (syntactic sugar) : >> >> select a., b. >> from author_books ab, author a, books b >> where a.author_id = ab.author_id >> and b.book_isbn = ab.book_isbn >> >> On Tue, 27 Aug 2019 at 15:52, David Raymond david.raym...@tomtom.com >> wrote: >> >> > It does support natural joins. <Insert usual "be careful with schema >> > changes" comments here> >> > USING needs parenthesis around the column list: ...using >> > (author_id)...using (book_isbn)... >> > -----Original Message----- >> > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On >> > Behalf Of Dominique Devienne >> > Sent: Tuesday, August 27, 2019 10:08 AM >> > To: SQLite mailing list sqlite-users@mailinglists.sqlite.org >> > Subject: Re: [sqlite] Query for Many to Many >> > On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne ddevie...@gmail.com >> > wrote: >> > >> > > select author., books. >> > > from author_books >> > > join author on author.author_id = author_books.author_id >> > > join books on books.book_isbn = author_books.book_isbn >> > >> > Which can also be written: >> > select author., books. >> > from author_books >> > join author using author_id >> > join books using book_isbn >> > Or even: >> > select author., books. >> > from author_books >> > natural join author >> > natural join books >> > All of the above untested of course :). >> > Not even sure SQLite supports natural join or not (I'd guess it does). >--DD >> > https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on >> > https://stackoverflow.com/questions/8696383/difference-between-natural- >join-and-inner-join >> > >> > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
