I don't think anyone would ever say they want "id, id, id"? They would say they want "customer_id, order_id, item_id" or some such. You would then probably say, "we just use 'id' for the id of every item..." but many places that I've worked actually explicitly didn't do that for essentially this reason. Natural joins on "foo_id" "just work" if you don't give every table the same "id" column.

Current SELECT syntax does allow one to "SELECT user.id as user_id, product.id as product_id..." which can then even autocomplete the FROM for you from your query "declaration" (a-la function declaration, in particular its return type).

No, this assumes you know what you want for a query. Which, seems largely fair?

Like, how would you send this question to someone? Or how would you expect it to be sent to you? If your boss doesn't tell you from what table they want some data, do you just not answer?

And sure, there could be ambiguities here. But these are not really fixed by listing the sources first? You would almost certainly need to augment the select to give the columns names that disambiguate for their uses. For example, when you want the teacher and the student names, both.

And this is ignoring more complications you get from normalized data that is just flat out hard to deal with. I want a teacher/student combination, but only for this specific class and term, as an example. Whether you start the from at the student, the class, or the term rosters feels somewhat immaterial to how you want to pull all of that data together for why ever you are querying it.