Requiring the WHERE clause in DELETE FROM would probably have avoided some very expensive disasters, too...

I'll just come out and say it, modern SQL amazing and powerful but it is not a good language. The language spec shows a weird obsession with "natural language" style programming, sometimes at the cost of safety, consistency and expressiveness. It is not a good language to generate by code, nor to use directly on the command line. It is not a good language to efficiently access your stored data, nor to express relational algebra. But you're going to have to learn it, and modern databases do amazing things with it to make it work.

Agreed, I've always thought this, CTE's kind of do this, but unfortunately Postgres does not optimise them (one of it's few failings). The SQL standard should be changed to optionally allow FROM to come first. SQl is amazingly powerful, sometimes over verbose though e.g windows functions. Also Natural join should be improved, with an option to use Foreign Keys to determine columns to join on, instead of just common column names which can be risky.

It makes sense for the simple case but I can see it getting a bit hairy if you're doing a variety of joins.

Then it would be FROM X join Y on .. join Z on .. join (..) as T SELECT X.A, Y.B WHERE ..

I guess it's jarring but that's due to me not being used to it. Some people prefer doing joins using there where clause in which case the FROM ends up being in closer to location to the WHERE.

I have thought the same thing, because it would match the order of other statements (insert, update, and delete) which begin with the source.

In fact I would put the select after the where clause:

    from t
    where x = 'y'
    select a, b, c
    order by a, b, c