I agree. The SQL language is in desperate need of an "upgrade" to a proper functional language. It's missing
so many basic features that it's just painful.
For example, why do I have to repeat expressions in the SELECT, GROUP BY and ORDER BY clauses!?
E.g:
SELECT
LEFT(Foo,4) as Prefix,
COUNT(1) as N
FROM MyTable
GROUP BY LEFT(Foo,4)
ORDER BY LEFT(Foo,4)
This gets really obnoxious for complex expressions. I mean sure, I can break things out into functions, or use the WITH clause, but both of those often end up being
more verbose, not less. This is solved in functional languages such as Haskell with the "where" or "let" clauses (not to be confused with the SQL WHERE filter).
I wish I could do something like:
SELECT
Prefix,
COUNT(1) as N
FROM MyTable
GROUP BY Prefix
ORDER BY Prefix
LET Prefix = LEFT(Foo,4)
Similarly, it's crazy to me that most SQL dialects don't allow relational functions to be passed around as proper data types. This kind of thing comes up a lot when implementing row-level security.
I'd like to be able to write "view functions" that can take any relation as an input, as long as it contains some column, and then add on some joins or filters based on that column. Imagine you have a bunch of tables that link to the "Customers" table via a "CustomerID" column or whatever. It would be great to be able to natively write a query that says "AddCustomerName" or "FilterDisabledCustomers". These would take an existing relation (table/view/function) as an input, and return a new relation as the output with an extra column or filtered rows.
This kind of modular style made up of small chunks of code that can be elegantly composed is just not possible or very messy in most SQL platforms. It's typical of most modern functional languages, and I think the "next gen" query language that replaces SQL will look more like Haskell and less like COBOL.