[paozac]

Not sure about this, I wouldn't like having to look up the FK name every time or hope it was named following the convention.

The first thing (among many others) that I would change in SQL is the position of the SELECT clause:

FROM .. JOIN .. SELECT .. WHERE ..

instead of

SELECT .. FROM .. JOIN .. WHERE ..

That would make the construction of many queries more natural.

[JoelJacobson]

Never thought about the order, but now when you mention it, I realize I always write

    SELECT
    FROM

and start writing the FROM clause first.

So if we could rewrite history, I agree the order you suggest would make more sense, but it’s probably unrealistic to change it, except for entirely new SQL inspired languages.

To comment on having to look up foreign keys. The idea I had in mind is to allow changing the default formatting of foreign key names, so you could figure out the name, except in special cases such as if there would be two foreign keys referencing the same table. For such cases you should explicitly name the foreign keys.

[tejtm]

Following the granularity gradient

FROM .. JOIN .. WHERE .. SELECT

tables -> columns -> rows -> filter

I think they knew that but decided they wanted DBAs to think about what they wanted to end up with before they began writing code, so they moved the last step to the first step. I can get behind that from a pedagogical perspective.

However today, relational databases are far too ubiquitous to always get the level of serious thought and considerations they were once afforded, and are regularly being used by (perish the thought) non-DBAs.

At this point it is muscle memory to start with:

   select count(*)
     from ..
     join .. on ..
 

then comment out the `--count(*)` and build up the output after the body works because I may not know what is available to select before isolating it.

[alserio]

Those were the times where "natural language like" was considered good and useful for use by non experts. It's one of the reasons why the sql syntax is so complex and, I believe, the reason why IBM did go with that order

[shazzdeeds]

This is effectively what CTE style syntax gives you. I always find them more intuitive for intermediate to advanced queries for exactly this reason. They’re also much easier for another reader to later come in and deduce what the query is doing.

[jansommer]

CTE's are indeed nice, but can also make your queries substantially slower, at least in Postgres

[DangitBobby]

That's mainly true because you don't have the benefit of an index when you join on a CTE, isn't it?

[jansommer]

It'a slow even without indexes. In other cases, where the CTE is used multiple times in the same query, it can be faster

[zamalek]

A JOIN is simply a special predicate.

    FROM A ... FROM B ... SELECT ... OUTER JOIN A.Foo = B.Bar ... WHERE ...

That also happens to incredibly compatible with code completion.

[BeefWellington]

The issue here is that FROM, WHERE, etc., are all optional, where the operation (SELECT in this case) is not.

If it were a proposal for a SQL21 standard to offer this as an optional method for query processing, I'd be all for it.

However, the idea of "non-optional followed by optional" came from an era where that sort of thing mattered and it made sense.

[emidln]

This is a good use case for an AST-based query compiler which would allow construction of clauses in arbitrary order. Using HoneySQL in Clojure, I frequently would write:

  (-> (from [:cities :c])
      (join [:population :c] [:= :c.name :p.city-name])
      (select :c.name :c.pizza-rank :p.population)
      (order-by [:c.pizza-rank :asc]))

It's handy for a lot of reasons, but unless I had mostly static queries (changing just some where clause params), I would always seek out a AST library rather than attempt string building for a SQL use case.