I do wish someone would create a compile-to-sql language that adds variables and basic conditional support. I think T-SQL (SQL Server) has a lot of this, but it's missing in the Open Source world.
Every database with a procedural language based on SQL (Oracle PL/SQL, Postgres PL/pgSQL, etc.) has it, T-SQL just doesn’t segregate procedural and declarative code the way most engines do. Mostly, I think this is a negative for T-SQL, but sometimes segregation means there’s no way to do one-off procedural scripts without making and then calling a procedure; but Postgres, for example, since 9.0 has the DO statement for this use case (which supports any procedural language installed in the DB, but defaults to PL/pgSQL.)
I can write a query that queries the result of another query, but I can't store a query and then pass that somewhere to query from.
Check the example at the beginning of https://www.postgresql.org/docs/13/queries-with.html
Postgres before it was called 'PostgreSQL', used something called QUEL, which is quite a bit cleaner. There is also Microsoft's LINQ.
A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.
SQL has always been a sort a magical, black-box in that you have very limited control over the query planner, and have to hint at it to do the right thing. (I guess `EXPLAIN` allows you to peak in the box a little)
I found [Apache Calcite](https://calcite.apache.org/docs/algebra.html) rather interesting. It provides the primitives that a DBMS is built from.
I would be interested to compare how PSQL is built in comparison. I would love a more layered/pluggable database that everyone can build off of. I think FoundationDB had this approach.
SQL could benefit from being language-integrated, yes. Very few languages can model relational algebra types though, if thats a thing you care about (it certainly is for me).
Now let me count the ways in which SQL is bad:
- it composes poorly due to its unwieldy cobolesque syntax
- it is a leaky abstraction revealing a lot of underlying implementation tradeoffs
- it doesn’t properly implement Cobb’s relational model
- it is poorly standardized with a ton of proprietary extensions and alterations present in virtually every implementation
- it is still poorly supported by tools because the model metadata lacks any standard interface to make universal tooling possible
A lot of the praise for SQL is just bandwagon hopping and cargo cult behaviour or a lack of vision by most people of how things could be much better