> On Aug 28, 2018, at 1:22 PM, David Raymond <david.raym...@tomtom.com> wrote: > > Embarrassing confession time: I didn't think you could use "using" to do this > while selecting "a.*" > > https://www.sqlite.org/lang_select.html > "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." > > I thought it literally took that column out of the result set. So "a SOME > SORT OF JOIN b USING (foo)" literally got rid of a.foo and b.foo and put the > value of a.foo into a "foo" column, or literally got rid of b.foo. And that > trying to select a.foo, or especially b.foo would raise an error of it not > being an existing column. > > I didn't realize that the column omission <only> happens with you "select * > from" and not for anything else. > > The more you know.
Yeah, this relates to Relational Theory, SQL, and how the two are almost, sorta, kinda, but not-really the same. One thing to keep in mind is that the source columns and the “working set” of columns (SELECT *) are not the same. You can always explicitly name a source column. While USING “collapses” the two columns in the working set (as does a NATURAL JOIN), it does not eliminate the ability to explicitly reference a specific source column using the table notation… that’s why the WHERE clause in my example works as well. And that’s kind of what the <table>.* notation does, just brings in all the columns from a specific source table. In a NATURAL JOIN (or a standard [CROSS] JOIN...USING) the two columns will always be the same, so there is little need to explicitly reference a source column. That’s not true with OUTER JOINs, however, but as shown that can be really useful. -j _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
