When you dig into it, the powerful idea here is the relational algebra, and its equivalence to a first-orderish logic.
I put up with SQL so I can use relations, and I love Postgres because it has the least bad SQL (by a mile!)
But SQL is a terrible, no good, very bad language.
I don’t really understand why folks who love the relational model aren’t perpetually up in arms about SQL being their only option. Much better query languages are known and well studied.
I, for one, quite agree. The advances in languages have been marvelous, and it's hard to imagine anyone today choosing to code in COBOL, or any other English-like language. SQL holds its place because of the tools it allows us to use, not on its own virtues... rather like Javascript with browsers.
And the problem seems very serious to me. In the words of Alfred North Whitehead, "By relieving the brain of all unnecessary work, a good notation sets it free to concentrate on more advanced problems, and in effect increases the mental power of the race." Conversely, a tool which imposes needless overhead is an IQ tax we can ill-afford.
So far, though, that's just my unsupported opinion, and one can't expect to convince people with generalities. But what a sprawling topic! You could make dozens of suggestions for improvements, any one of which would need an extensive conversation.
Here's one that I think is simple: why would we want a language where the clauses must come in a particular order? `FROM mytable SELECT column` is as clear an expression as `SELECT column FROM mytable`, and probably better, in that it starts with the source and winnows from there. Likewise, the order of WHERE, ORDER BY, and so on don't change what is being said.
I believe the "why" is, "because parsing SQL is hard enough already", but that's a problem unto itself. A language with a more regular syntax is easier to work with and improve.
Now, while I'm not at all saying this is ideal, consider something as simple as a shell:
cat mytable | cols x y z | where x > 2 | join table_2.y = mytable.y | sort z
The parts are atomic, and the structure is easy to see. If you wanted to add a "command", you aren't going to run into questions of how to shoehorn it into the existing language. Even if the example solution isn't to one's taste, I hope the general point stands apart from it.
Also, I think it only fair to say: PostgreSQL has done quite a remarkable job of polishing SQL into the best thing which can be made of it. I may not be a fan of the language itself, but I'm truly grateful when I'm able to use PG's dialect rather than the others I need to work with.
