- No non-nullable types (at the expression level). No way to express e.g. normal boolean logic
- No real data structures (at the expression level), unless you count rows, which are not first-class values. Even collections aren't first-class
- Very awkward control flow constructs. E.g. look at how you write recursive queries. Even if/else is weird. It's a classical Turing Tarpit: everything is possible but nothing is easy
- Absurdly complex grammar. Something like 200+ keywords. Bizarre and inconsistent quoting rules
- Weird rules about where expressions can be used, and where you have to put variables. Often if you want to extract out a common subexpression and reuse it you have to completely restructure your statement.
- Missing basic functionality, e.g. no string concatenation operator (except in some nonstandard dialects)
- No libraries. No standard way of reusing code. No unit testing framework, which means in practice most projects have no testing.
It's a terrible language by any reasonable standard. Its closest cousin is COBOL, and that's honestly less awful to write.
That's no excuse for making it bad.
> No Libraries? There are a zillion battle tested libs out there.
Not in SQL.
> And why you would unit test datasets in a database?
For the same reason as anywhere else.
> Most if not all software stacks have mock libs available.
Exactly. They're a basic piece of functionality you'd expect most languages to provide, but not SQL, because it's awful.
`||` is the SQL standard. IMO its biggest deficiency is a single NULL nullifies the whole thing. But at least that's consistent with how NULL behaves in many other cases.
I would love to be able to say e.g.: this column is either a PK in the “results” table or a PK in the “errors” table.
We have dozens of programming languages in use for general programming, it is a field that is constantly evolving. Even JS, which is hard to change because that's what browsers run and you don't control your client's browser is seeing some evolution, using transpilers, and now WebAssembly.
But for databases, there is only one, and that SQL. There are some alternatives, but no one comes close to SQL in terms of usage. So maybe SQL is not that bad after all.
Maybe the reason is that the relational model is really good, and attempts to deviate from this will only work in a niche. That a declarative style is also really good, and again, you won't have much success if you deviate from this. And if you end up doing SQL with a different syntax, for most people, it will be too small of an improvement to justify changing their way.
I wish Postgres had a stable, more low-level API than SQL. Perhaps something that resembles the query plan I get from EXPLAIN. An application written against this API could implement an SQL DB: (1) parse SQL (2) implement a query planner that spits out a query plan written against this API.
The problem with databases is actually complexity. Any individual feature is more or less safe, but around the time reliability, caching and indexes get matched together there is a complexity explosion and it doesn't (normally, anyhow) make sense to implement a domain-specific DB (call is a DSD?).
But, around the time a company has invested in implementing those 3 features and discovered that it has sunk a lot of resources into the DSD, is politically averse to recommending it be stripped out and there is a high real cost to taking out the tech debt in one go.
Really the problem here is SQL's syntax. If using a basic relational database was a pleasant experience that involved some familiar C-like syntax instead of broken English people would be more tempted to go with a DB instead of rolling their own. The NoSQL databases were a good step in that direction, but then they by and large overfocused on big data instead of everyday usefulness. Things like Redis took hold which is nice.
Making it easy to run SQL is a reasonable approach, but the problem is that the good databases - I like postgres - are SQL native and it is hard to get efficiency without speaking the DB's language. We really need a PostgresPostSQL database that is a perfect postgres clone but primary parser supports a language with good syntax.