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
