Why SQL syntax sucks, and why it matters | Hacker News Reader

Why SQL syntax sucks, and why it matters

carlineng.com

3 pointscarlineng7 comments

Yes, SQL isn't good for data analysis. I recently had to help one of our data analysts port some code from Impala to Hive, and the query they gave me was terrifying: a dozen CTEs strung together in a query that was literally hundreds of lines long.

However, I think we already have an answer to that complexity, and it's not Malloy, but data frames. With data frames you can split your query into logical steps, each of which can be debugged and examined without having to rewrite the query like you have to with CTEs.

You probably know that the earliest proponents of the relational model criticized SQL and still do. Chris Date and Hugh Darwen pushed the Tutorial D query language; Date has used that in a couple of his relational database books and in his seminars. Both experts and regular users have recognized the flaws and warts of the SQL language for decades. Nevertheless SQL remains standing with no serious replacement in sight, I think for obvious reasons:

- Vast amounts of SQL code already written, with widespread language and library support.

- The various relational database vendors long ago settled on various flavors of SQL, and SQL has an ISO/ANSI standard. While not entirely compatible across vendors the commercial and open source SQL dialects have mostly stayed "close enough" to allow changing database engines (though that rarely actually happens in my experience).

- Oracle has led the relational database business for decades, they have a big investment in their flavor of SQL and no incentive to disrupt their customer base.

It often happens in the software world that a language or tool catches on and gets widespread use, and then in retrospect we see the flaws. Fixing those flaws, or migrating to a different language or tool, has higher costs and risks (actual or perceived) than living with the flaws. Leaving the mainstream of relational databases may work for startups and hobby projects, but at enterprise scale you can't get any traction trying to shift from a well-understood and working language to an unproven competitor.

To paraphrase Steve Jobs, a SQL replacement would have to offer an order of magnitude improvement over SQL to get attention, not just incremental or cosmetic improvements. The relational model and SQL did offer that order of magnitude improvement over what we had before (and I remember, my career started before relational databases). None of the replacements or alternatives that have come along since the '80s show huge improvements along any dimension except in some niche cases.

I write SQL every day, and have for many years. Sometimes the language annoys me, but I can live with that -- I see it as a tool rather than a lifestyle choice. When I mentor programmers or teach someone relational databases and SQL and see them struggle I think that happens because they have not formed the correct mental model of relational databases, not because of flaws in the SQL language.

The post itself provides a very real example of a flaw with the SQL language beyond simple aesthetics. Namely, that typical SQL queries only represent data in rows and columns, yet data and queries are often multidimensional. Any query that must represent data aggregated at different granularities produces output that is difficult and dangerous to use.

I think an analogous technology to Malloy is Typescript. It provides very real benefits over Javascript, but I don't think anyone would argue that it's 10x better. There will always be a lot of Javascript (and SQL) in the world.

I don't argue that SQL doesn't have flaws. Nor do I argue that some query results prove hard to present and interpret. Nothing about the relational model limits relations to rows/columns, but by default SQL presents results in that format. A large number of database queries do follow the useful rows/columns or records/attributes pattern. Some don't and for those numerous data analysis and visualization tools that sit on top of SQL exist and have for a long time.

This is a follow up to my last post comparing SQL to the new Malloy query language, and a response to some of the HN commentary I received around it.

... I'm writing a CTE right now and it is not fun.

I am waiting for a specific instance of some ChatGPT system that can absorb individual table logic and then lets me build queries in addition to pre estimating the time, resource loads, and costs involved once you say go.

Reply on news.ycombinator.com