Tl;dr: the original design idea behind sql was a failure and here’s why.
I know the intent was to write a declarative language where you just specify what you want and the system figures out how to get it- but I consider that project to have definitely failed considering that in order to get the sql interpreter to behave in a sane fashion you must use explicit JOIN statements everywhere and fetch table rows and index them to other table rows in a specific order using specific keys that the programmer chooses. It’s exactly the same as writing while loops, though the syntax is admittedly quite nice.
I’ve seen people try to interact with the interpreter with a great big god query like SELECT FROM A,B,C,D,E WHERE A.id=B.id and B.id2=C.id2 and so on, and that would be a declarative way to do it, but the performance of the interpreter in such scenarios is absolutely, universally, perplexingly dismal and in practice you can’t use sql that way. It was a nice idea though.
Sorry but I've to agree with the other flippant response to this post. The whole idea of SQL is to avoid such imperative thinking, and SQL optimizers are incredibly good in most prominent DB engines. There are ways to hint and influence a certain query plan; but that's not to take away from the incredible convenience that SQL affords for writing adhoc queries.
The ones I’ve used (mainly Microsoft sql server) are not just bad, they’re abysmally bad for any query that’s not extremely trivial. Writing every query with explicit joins is a necessity almost 100% of the time. Maybe you’ve just never tried to do anything tricky with large tables with a lot of data? Query optimizers almost always resort to nested table scans as soon as you do anything even slightly complex.
I know the intent was to write a declarative language where you just specify what you want and the system figures out how to get it- but I consider that project to have definitely failed considering that in order to get the sql interpreter to behave in a sane fashion you must use explicit JOIN statements everywhere and fetch table rows and index them to other table rows in a specific order using specific keys that the programmer chooses. It’s exactly the same as writing while loops, though the syntax is admittedly quite nice.
I’ve seen people try to interact with the interpreter with a great big god query like SELECT FROM A,B,C,D,E WHERE A.id=B.id and B.id2=C.id2 and so on, and that would be a declarative way to do it, but the performance of the interpreter in such scenarios is absolutely, universally, perplexingly dismal and in practice you can’t use sql that way. It was a nice idea though.