I am the author of a small parser/typechecker for a subset[0] of SQL, roughly matching that supported by SQLite, and I agree with you COMPLETELY. What follows is my rant directed at those who don't.
It is an ugly, verbose language. You can be very familiar with thinking in sets, and still not like SQL.
It's what we've got, and the SQL databases available are very, very good products. But I do wish a cleaner language could have won.
As you know, anyone who's used LINQ in C#, particularly by directly calling the extension methods .Select(...).Where(...).OrderBy(...),
sees how much better it is from a composability standpoint.
SQL is the anti-Lisp. Lisp's design is about 4 pieces of syntax and a few fundamental operations from which all else is built.
Conversely, nearly every operation in SQL is a tacked-on special case to the ridiculously complex SELECT syntax.
Filtering results? That's a clause of SELECT.
Ordering? Clause of SELECT.
Filtering after aggregating? Oh, that's a different clause of SELECT.
Once this philosophy has infected the brain of a SQL implementer, it spreads like wildfire.
That's why you even see custom syntax pop up even in good'ol function calls sometimes, like in Postgres: overlay('abcdef' placing 'wt' from 3 for 2).
SQL fans often talk about the beauty of relational algebra. Once you achieve relational enlightenment, SQL is supposed to be beautiful.
But if we wrote math like SQL, you wouldn't say 2 * 3 + 4. There would be a grand COMPUTE statement with clauses for each operation you could wish to perform. So you'd write COMPUTE MULTIPLY 2 ADD 4 FROM 3. Of course, the COMPUTE statement is a pipeline, and multiplication comes after addition in the pipeline, so if you wanted to represent 2 * (3 + 4) you'll push that into a sub-compute, like COMPUTE MULTIPLY 2 FROM (COMPUTE MULTIPLY 1 ADD 4 FROM 3).
SQL clauses could have been "functions" with well-defined input and output types, if the language designers had come up with a type system to match the relational algebra.
WHERE - table<row type> -> predicate<row type> -> table<row type>
JOIN - table<row x> -> table<row y> -> predicate<row x, row y> -> table<row x, row y>
ORDER - table<row type> -> list<expression<row type>> -> table<row type>
These could be pipelined, rather than nested, either with an OO-style method call syntax or a functional style pipe operator.
You understand the idea. Again, as you've mentioned, the LINQ methods[1] are a great resource for people not familiar with this style.
But, counterargument. Languages with minimal syntax and great power are often claimed to be unreadable.
Sometimes it's nice to have special syntax, to help give a recognizable shape to what you're reading,
instead of it being operator / function call soup.
So what did SQL accomplish by making everything a special case of SELECT?
Well, it's got this sensible flow to every statement. You see, the execution of SELECT logically flows as I've numbered the lines below.
SELECT
8 DISTINCT
7 TOP n
5 column expressions
1 FROM tables
2 WHERE predicate
3 GROUP BY expressions
4 HAVING expressions
6 ORDER BY ...
I sure am glad they cleared that up. If it had been a chain of individual operations I'd have been utterly baffled.
Ignoring completely the syntactical design, the lack of basic operations is a pain too.
Why can't I declare variables within queries?
I often would like to do something like:
select
let x = compute_something(...)
in
x + q as column1
x + r as column2
from ...
Instead, when I really need that, I end up wrapping the whole thing in an outer query and computing X in the inner query.
Hooray for SELECT, the answer to all problems!
Oh and yes, views and stored procedures and UDFs are no answer to the need for one-off local composability within queries.
Then you have the sloppy design of the type system in virtually all SQL dialects. SQL Server doesn't even have a boolean type.
There is no type you can declare for `@x` that will let you `set @x = (1 <> 2)`.
And don't even get me started on the GROUP BY clause, the design of which contorts the whole rest of the language.
If you GROUP BY some columns, you must not refer to any non-grouped columns in the later parts of your query (refer to my table above for which parts are "later").
Unless, that is, you are referring to them in aggregates. Then the HAVING clause was tacked on so that you'd have a way to do a filter -- the same thing as WHERE -- after the GROUP BY.
Does it all make sense once you understand it? Yes, in that you can see how you'd end up with this system if you were adding things piece by piece and never went back to redesign from square one.
Wow, I have a lot of ranting to do about SQL. I feel like I haven't even scratched the surface.
And hell, I still pick SQL databases every time I start a project! The damn language is useful enough and the products work great.
But it has all the design elegance of the US tax code.
[0] https://github.com/rspeele/Rezoom.SQL
[1] https://docs.microsoft.com/en-us/dotnet/api/system.linq.enum...