Basically, abstraction.
SQL requires your clients to know your exact database structure, which is not good. On top of that, analysing the SQL in order to perform special operations based on the value sent as the input is a really difficult thing to do. There are entire softwares which are pretty much responsible only for that. Do you know what those are? If you have guessed the databases, you are right.
Thanks to not exposing the SQL directly, you are not limiting the consumer of the API to the internal representation of your database. You easily expose only what you want to expose.
And since clients of the API depend only on the abstraction, you are free to have as many layers as possible between the API input and the actual database (security, caching, loading data from multiple databases on a single request,...).
For public services, exposing a database directly is pretty much never the right approach. If you however have a few internal systems, sure, your approach might make sense but even then it might just be easier to connect to application A's database directly from Application B by giving the database credentials to the Application B, rather than trying to come up with a custom HTTP interface for the database SQL language.
Why can't I just compare the URL (or SQL query) against keys in Redis
before performing the actual query on the RDBMS?
Because it's not easy. Even if someone uses a very simple query, such as:
SELECT st.id, jt.name
FROM some_table st
INNER JOIN join_table jt ON jt.some_table_id = st.id
WHERE st.name = 'hello
world' AND st.type = 'STANDARD'
how do you make sure the result is properly cached? This query includes newlines, but someone could just as well write the query in the following way:
SELECT st.id, jt.name FROM some_table st INNER JOIN join_table jt ON jt.some_table_id = st.id WHERE st.name = 'hello
world' AND st.type = 'STANDARD'
and it's still supposed to be cached in the same way as the one above. I have specifically included a where in which a string search contains a new line, so simply finding line endings and replacing them with a space is not going to work here, parsing the request correctly would be much more complicated.
And even if you do fix that, another query could switch the order of conditions and the query would look like this:
SELECT st.id, jt.name
FROM some_table st
INNER JOIN join_table jt ON jt.some_table_id = st.id
WHERE st.type = 'STANDARD' AND st.name = 'hello
world'
and another request could contain a redundant WHERE argument, like this:
SELECT st.id, jt.name
FROM some_table st
INNER JOIN join_table jt ON jt.some_table_id = st.id
WHERE st.type = 'STANDARD' AND st.name = 'hello
world' AND st.stype = 'STANDARD'
All of those queries are still supposed to return the same result, should be cached in the same way. But handling all of the possible options is pretty much impossible. That's why you cannot simply compare the URL against keys in Redis.
Best Answer
Each database vendor has their own set of proprietary SQL extensions to achieve certain piecemeal goals. But none of these extensions have achieved widespread adoption like SQL has.
If your goal is to create enhanced functionality in a standards-based way, you have two options. You can either go to the SQL standards committee and make your case (which could take years), or you can create your own standard and hope the community adopts it. Vendor extensions on the SQL standard have not succeeded in this way.
So Microsoft, instead of taking a piecemeal approach, decided to go all in and create a comprehensive solution. And it worked; the industry adopted it as the go-to solution for OLAP querying. Ergo, a defacto standard, without the committee pain.
MDX is a de-facto standard, created by a single vendor. Assuming you can get broad buy-in, you have eliminated the need for design by committee, and saved yourself a lot of time, effort and money.