it's kind of a catch-22, right? SQL is declarative. And while people seem to love declarative DSLs, they are generally really only awesome on the happy path. For SQL, sometimes, it can be incredibly obtuse when something goes horribly wrong (in terms of query speed) -- even with great tools like EXPLAIN. And a query that is fast on one SQL might not be so fast on another SQL.
For other declarative DSLs, I've definitely bashed my head against the wall and gotten a few gray hairs over "why the f* doesn't this goddamn thing that should work work". Sometimes it's because I'm not grokking how the platform works. Sometimes it's a bug, or unimplemented feature.
I suspect the reason why people don't absolutely abhor SQL is that the biggest players (MySQL, Postgres, Sqlite) have actually done a reasonably good job of making the 98% paths very good and 90% of the rest "good enough".
> the biggest players (MySQL, Postgres, Sqlite) have actually done a reasonably good job of making the 98% paths very good and 90% of the rest "good enough".
Exactly - Unless you are doing something really unusual (or wrong), the vendors of these engines have almost certainly encountered and optimized for some shape approximating your scenario.
If you want to get into some extreme ends of the practice, "ancient" engines like DB2 are some of the most capable. Many have been around longer than most developers today have been alive (myself included). That is a lot of optimization legacy to argue against. The halloween problem & iceberg meme comes quickly to mind. Why wouldn't you want to stick with something that has already dealt with all of that bullshit?
Eh, if you're at the point where you're doing stuff like "give me all records where a one-to-many has exactly two entries, and a different one to many has at least one record with property x" it gets VERY hard to do the correct performant SQL. I don't think that sort of query is too bizzare.
For other declarative DSLs, I've definitely bashed my head against the wall and gotten a few gray hairs over "why the f* doesn't this goddamn thing that should work work". Sometimes it's because I'm not grokking how the platform works. Sometimes it's a bug, or unimplemented feature.
I suspect the reason why people don't absolutely abhor SQL is that the biggest players (MySQL, Postgres, Sqlite) have actually done a reasonably good job of making the 98% paths very good and 90% of the rest "good enough".