For my own clarification, the statements quoted way down below aren't exactly equivalent, correct?
"For each pair of columns identified by a USING clause, the column from the right-hand dataset is omitted from the joined dataset. This is the only difference between a USING clause and its equivalent ON constraint." (Short version: USING will return fewer fields than ON, and order can matter for both types because of affinity/collation) So if you have tables tA and tB (ID integer, FirstName text COLLATE NOCASE, LastName text COLLATE NOCASE)... with contents tA: (1, 'john', 'smith') tB: (2, 'JOHN', 'SMIth') then select * from tA inner join tB USING (FirstName, LastName); gives 1, 'john', 'smith', 2 select * from tB inner join tA USING (FirstName, LastName); gives 2, 'JOHN', 'SMIth', 1 and select * from tA inner join tB on tA.Firstname = tB.FirstName and tA.LastName = tB.LastName; gives 1, 'john', 'smith', 2, 'JOHN', 'SMIth' And let's say tB did NOT have the COLLATE NOCASE, then select * from tA inner join tB USING (FirstName, LastName); would use tA's NOCASE collation (first table listed) for all fields and return the 1 record, whereas select * from tA inner join tB ON tB.FirstName = tA.FirstName and tB.LastName = tA.LastName; would use tB's BINARY collation (tB on the left side of the =) and return no records. -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Tuesday, March 08, 2016 12:11 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] CTE for a noob On 2016/03/08 5:02 AM, Stephen Chrzanowski wrote: > Now I'll have to use USING a bit more often to get the drift and get > out of this multi-call thing. I sort of see what is going on here, > but practice is whats needed. "USING" has three uses in SQLite, first to enlist a virtual table, secondly to suggest an Index to the Query Planner and thirdly as in the example Igor gave where "USING" is simply short-hand for a join where the joining index fields are simple and named the same. This is defined in the standard as a join operation, by the way, works everywhere so not special to SQLite. Easiest is probably by dual example - these two statements are equivalent: SELECT * FROM tA JOIN tB USING (ID) vs. SELECT * FROM tA JOIN tB ON tB.ID = tA.ID Of course this next query can't be simplified since the field-names do not match: SELECT * FROM tA JOIN tB ON tB.ParentID = tA.ID which might make the "USING" thing seem a bit overrated at first glance, but consider the following equivalent queries to see its simplifying power: SELECT * FROM tA JOIN tB ON tB.Surname = tA.Surname AND tB.FirstName = tA.FirstName AND tB.DateOfBirth = tA.DateOfBirth vs. SELECT * FROM tA JOIN tB USING (Surname, FirstName, DateOfbirth) the basic format of which, I might add, covers a very large percentage of typical joined queries. Cheers! Ryan _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
