On 22/06/2020 3:25 pm, Laurenz Albe wrote:
Then there is the case of "a JOIN b USING (somecol)".
Here, "somecol" will appear in the output only once. How should it be
labeled? If you say "not at all", then what do you want to happen for
SELECT * FROM a JOIN b USING (id) JOIN c USING (x)
where all three tables have a column "id"?
Thanks, I've learned a tip about USING :) In such case, could it not simply return all columns prefixed with their table/alias names, as that is what the table-prefix option would mean? To pretend-modify the documentation: "Also,
USING implies that only one of each pair of equivalent columns will be included in the join output, not both;
unless table-prefixing is enabled for one or both of columns." In other words, if table-prefixing is enabled for a column in a USING clause, it behaves like a regular LEFT JOIN. So SELECT #* FROM a JOIN b USING (id) would give:
a.id, a.title, b.id, b.title
But the real objection I have is that the problem can be easily avoided
by spelling out the SELECT list and using aliases. Either you are talking
about ad-hoc queries, or you want to use "SELECT *" in your code.
- For ad-hoc queries I don't see the problem so much. Here, the tables will be returned in the order you specify them in the query. So if you have "b JOIN a", the result columns will always be first all columns from "b", then all columns from "a". So you can easily figure out which column belongs to which table.
True, but it would be nice to have an easy 'foolproof' way to see the table name a column belongs to (especially for wide tables).
- For queries in your code, using "SELECT *" is a very bad idea. There is a real risk of your code breaking if the database changes. In code, you are probably not ready to deal with a changing number of columns.
Yes. Though in the case of explicit SELECT lists, duplicate column names usually requires an alias to be given (unless using column index), and this is often just the table prefix anyway, so it would be nice to be able to automatically prefix the table name in the query. It would be rather like the USING clause itself - just a nice convenience/shorthand to have.
Thanks for the comments!
