I don't need your query language

SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows.<p>And there are often problems when using multiple SELECT queries in a batched statement: you can&#x27;t re-use existing CTE queries. Not all client libraries support multiple result-sets. It&#x27;s essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn&#x27;t even support named result sets...), which means you can&#x27;t opportunistically skip or omit a SELECT query in a batch because your client reader won&#x27;t know how to parse&#x2F;interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you&#x27;ll run into concurrency issues if data changes between SELECT queries in the same batch ()<p>[1] <a href="https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;ef&#x2F;core&#x2F;performance&#x2F;efficient-querying#avoid-cartesian-explosion-when-loading-related-entities" rel="nofollow noreferrer">https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;ef&#x2F;core&#x2F;performance&#x2F;effici...</a> and <a href="https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;ef&#x2F;core&#x2F;querying&#x2F;single-split-queries" rel="nofollow noreferrer">https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;ef&#x2F;core&#x2F;querying&#x2F;single-sp...</a>

I think of SQL as one of the few good things we have in software development, so like the author I consider it best to try to do as much in SQL as possible.<p>It&#x27;s not too uncommon I run into code in other languages where I just don&#x27;t understand what it does, or to write code myself that behaves in ways that surprise me. That almost never happens in SQL.<p>Even a big hairball of a query just takes time to figure out (unless a database-specific function with odd behavior is used).

The main issue with sql is, that it is the wrong way around, which eliminates all tooling support.<p>You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that.<p>So switching this, moving from and joins in front of select, might be everything needed to fix sql.

The problem with SQL is that it is not a (very) composable language.<p>The documentation for EdgeDb goes into some detail about that and shows an alternative better language for data-queries.<p><a href="https:&#x2F;&#x2F;www.edgedb.com&#x2F;showcase&#x2F;edgeql" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.edgedb.com&#x2F;showcase&#x2F;edgeql</a><p>To understand why SQL is bad, you must first be shown something better, and EdgeDb seems to be such better more composable language.

As an experienced (===old) developer, I have learned that data long outlasts the programs that access it. The lifetime of data is measured in decades, but programs last for years. Most SQL-based RDBMS teams have figured out workable version migration paths allowing old data to run on newer servers. Because this kind of migration is a very common and economically valuable operation, the vendors make sure it works correctly.<p>Sometimes a project, especially a greenfield project, looks like it will benefit from more recently invented data storage and query tech than your grandmother&#x27;s SQL. That&#x27;s always possible. And as developers we hope for, and work for, continued progress. But consider what may happen when the project succeeds.<p>If you&#x27;re still on the project, you&#x27;ll wake up one day and realize your oldest data is 20 years old. What happens if your storage and query engines are also 20 years old, because they didn&#x27;t succeed to the extent needed to pay for maintenance and upgrades? You&#x27;ll be in the software equivalent of the century-old subway system where you have to make all your replacement parts yourself, or get gouged by vendors that can&#x27;t spread their costs among many customers.<p>Build for the ages, not for the moment!

Show me an elegant SQL version for the queries in this article: <a href="https:&#x2F;&#x2F;www.timestored.com&#x2F;b&#x2F;kdb-qsql-query-vs-sql&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.timestored.com&#x2F;b&#x2F;kdb-qsql-query-vs-sql&#x2F;</a> Particularly when you are trying to run queries where order matters, e.g. top 3 posters by topic on HN. You will find it much more annoying. Fundamentally SQL is based on the concept of tuples&#x2F;sets which have no order so there&#x27;s no way to avoid it being messy. What you want is a database based on the concept of an ordered list, suddenly what is complex in standard set SQL becomes easy in almost any other language. My second big complaint would be that SQL isn&#x27;t really a programming language. Parts have been bolted on by various vendors or they now let you run python&#x2F;java on the SQL server but considering how heavy SQL already is, having a full blown language may actually be less cognitive load than learning all the sub variations of language implementations.

I don’t really find the examples convincing. Like, I get that sql could maybe be written in a slightly less horrid way but I would prefer something a lot less horrid.<p>I think I’m much more motivated by analytics queries than the kinds of thing in this example though. I find sql is poorly suited in this case because it is verbose and written backwards, and often requires many layers of subqueries. That said, one can usually still express queries in SQL that other systems do not allow.<p>For these kinds of queries I think there are just better ways to express them. Another issue with sql is that has some quite strange semantics.[1]<p>An example query I wrote yesterday is:<p><pre><code> select group, min, max, (max-min)&#x2F;1e9 range from (select group, min(size) min, max(size) max from (select time, instance, sum(size) size, regexp_replace(name,…) group from X group by regexp_replace(name,…), time, instance) group by group) order by range desc limit 10 </code></pre> Which is neither pleasant to write nor iterate on interactively.<p>With something like dplyr instead:<p><pre><code> X %&gt;% mutate(group=regexp_replace(name,…)) %&gt;% group_by(group,time,instance) %&gt;% summarize(size=sum(size)) %&gt;% group_by(group) %&gt;% summarize(min=min(size),max=max(size),range=(min-max)&#x2F;1e9) %&gt;% arrange(-range) %&gt;% head(n=10) </code></pre> And that can be built up interactively pretty easily by adding onto the end of the pipeline.<p>I would also note that, due to sql being painful, the query is not exactly the one I wanted and instead I would have wanted something better capturing the change over time, but the thought of doing that in SQL seemed too unpleasant.<p>An example of an actual query language that tries to be better for analytics: <a href="https:&#x2F;&#x2F;prql-lang.org&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;prql-lang.org&#x2F;</a><p>[1] from someone who spent a lot of time working on databases and sql: <a href="https:&#x2F;&#x2F;www.scattered-thoughts.net&#x2F;writing&#x2F;against-sql" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.scattered-thoughts.net&#x2F;writing&#x2F;against-sql</a> and just on semantics: <a href="https:&#x2F;&#x2F;www.scattered-thoughts.net&#x2F;writing&#x2F;select-wat-from-sql&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.scattered-thoughts.net&#x2F;writing&#x2F;select-wat-from-s...</a>

SQL is only ever as good as the schema relative to the business or problem domain. The focus on the syntax of the language was always a mystery to me. It&#x27;s a domain-<i>specific</i> language. It&#x27;s up to <i>you</i> to make it not suck.<p>If you are forced to work with a schema that is poorly-aligned with the logical reality it intends to represent, you would definitely walk away with a bad taste in your mouth. Hacking around bad normalization is 99% of what makes SQL suck for me.<p>If you ever get a chance to design the whole thing yourself from zero, you should almost always insist on one big database&#x2F;schema and routinely review the table structure with the business owners before you actually go to prod.<p>The moment you start doing things like putting data for service A into database A and service B into database B, you lose a lot of power. Sometimes this is required, but most of the time it&#x27;s an org-chart alignment meme. There are ways to join these separate databases, but it starts to fall down pretty quickly. The true magic of SQL is having all of those dimensions in <i>one</i> place at one moment in time so you can put a pin in anything without complex distributed transactions.

Also important: The behaviour of SQL is well understood, a new query language always introduces the risk of defects in the query language itself or developers making mistakes in an unfamiliar language.

&gt; Here is another common argument: SQL was designed with 1970s businessmen in mind, and it shows.<p>That is a funny way of looking at it. I see that SQL is based on the work of a computer scientist vs DSLs being made by hobbyists, and it shows.

I had a similar experience this month. We&#x27;ve been pair-programming using Dbt to write &quot;long-form&quot; SQL to bubble up a report to our business users.<p>After an initial &quot;Uh-oh, I haven&#x27;t manually written complex SQL in a while...&quot; it all came back fast enough (Thanks, first-semester relational algebra!). Turns out, sql <i>is</i> well-suited for business &quot;in-queries&quot;!<p>The things that made us scratch our heads came from how the schema had evolved over time. We now have those hairballs at least &#x27;contained&#x27; and visible. And it&#x27;s all pretty readable imho.<p>I guess my initial unease came from using ORMs for CRUD persistence and very rare exploration. And holy moly, I&#x27;m grateful for ORMs. I wouldn&#x27;t want to manually write those inserts and updates.<p>So, I guess it depends on what you want to accomplish with your database.<p>Btw: A HUGE shout-out to Dbt and Dbt cloud for letting us treat sql as code. Didn&#x27;t expect to love it that much. How was this not a thing earlier?

I&#x27;m working on a query language right now!<p>Why not SQL?<p>Lack of tooling for SQL.<p>Yes, SQL lacks tooling. There&#x27;s a ton of stuff to build a SQL client, obviously. However, on the other side:<p>- I have no sane way to parse SQL<p>- I have no sane way to comprehend SQL<p>Writing a SQL query system would be many months of work. Tossing together a good-enough query language with standards like JSON or YAML means I can json.loads(query) in Python and JSON.parse in JavaScript.<p>SQL would be an ideal fit if there was good tooling, and it fits in more places than most people realize. Web API query a whole bunch of stuff stored in all sorts of complex ways. SQL is better on paper than RESTful &#x2F; AJAXy &#x2F; GraphQL &#x2F; etc. APIs.<p>It&#x27;s not better if it means that the query language takes more time to build out than the entire rest of the system.<p>TL;DR: If you want to build a high-visibility open-source project and guarantee employment for the rest of your life, an elegant SQL parser, especially for building web APIs, would be a great thing to do.

We write programs with Python, Java, Rust, Javascript and so on. Yet we use a very different language, eg. SQL, to query and modify data. Why? Why don&#x27;t we use eg. Python as well? SQL is different from other languages: it is declarative, meaning it doesn&#x27;t dictate how to do it, but what the response should be. Maybe that&#x27;s the reason? But if that&#x27;s the case, why are declarative languages not more popular? SQL and other language are not compatible, and that&#x27;s often a problem. You have this hard border (related to impedance mismatch).<p>SQL (or GraphQL) is often used as a remote API: The client sends a SQL statement, the server processes it and sends the response. SQL is very powerful, but also dangerous: the statement might be very expensive, for example because an index is missing. Sure, you can shoot yourself in the foot also with Python or Java, but I argue it&#x27;s harder. SQL is one more technology in your stack, one more thing to learn. And actually, there are many many SQL dialects.<p>I wish databases have better, faster, and standardized support for a fast procedural language. So that clients can send programs, the server processes it and sends the response. A way to access tables and indexes like a hash table or ordered map. That way, there is no border. There is no slow query due to a missing index. You have to think about how data is access, which indexes are needed. But you have everything under your control. There is no risk of a missing index, or risk of the database not picking the index it should.<p>(I wrote 3 relational database engines and 4 SQL parsers: HypersonicSQL, H2 database, Apache Jackrabbit Oak, PointBase Micro. I also wrote a GraphQL parser and engine, and a Key-Value store. It&#x27;s not that I hate SQL.)

Having used (a lot) of Hive SQL, I absolutely <i>do</i> need your query language. SQL is fundamentally incapable of expressing any sort of abstraction, so non-trivial queries quickly become completely incomprehensible, unmaintainable and bug-prone.<p>Learning a new language is a one-time, up-front cost. Dealing with an awkward, inexpressive query language that integrates poorly with my main language, my types or my interface description languages is an ongoing source of painful friction. Learning something new should not be nearly the barrier to adoption that it seems to be for most people!<p>SQL&#x27;s shortcomings seem so clear and omnipresent that I legitimately do not understand why everybody seems so drawn to it. Are the usage patterns for code against a transactional database so different from the sort of Hive queries I&#x27;ve had to deal with for data engineering and machine learning? Is everybody happy with abstractions layered over SQL like ORMs?<p>Why can&#x27;t we have, I don&#x27;t know, some typed variant of Datalog or something instead?

I agree with the premise of the article, I think, but I find the &quot;good SQL&quot; versions... uncompelling.<p>(1) Switching `left join` to the default inner `join` changes query behavior. I&#x27;m guessing it&#x27;s intentional on the author&#x27;s part? But it feels like the wrong change to make when trying to compare syntax like-for-like.<p>(2) I am also in camp &quot;SQL keywords really don&#x27;t need to be uppercase&quot;, so keep fighting the good fight, brother. That said: it&#x27;s an uphill battle and far from universal. Most SQL &quot;formatters&quot; I&#x27;ve used automatically uppercase everything.<p>(3) Dropping the alias in `Actors.name AS actor_name` is another case where you&#x27;re not doing like-for-like. Just using `Actors.name` means, for example, the first example&#x27;s output table will have two columns: title and name. I&#x27;d argue for most uses title and actor_name are better output column names.<p>Those points aside, the primary simplification seems to be switching `join ... on` to `join ... using`. Big +1 from me on that.

In case anyone is wondering he is talking about EdgeDB (<a href="https:&#x2F;&#x2F;www.edgedb.com&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.edgedb.com&#x2F;</a>)

Funny how differently things can be framed.<p>You can either call SQL proven and battle-tested, or crusty and outdated, depending on your agenda.<p>Same for the fancy new alternative: It is either fresh and innovative, freed from the shackles of legacy and standard-compliance, or reinventing the wheel in a non-standardized manner.

FancyQL, which he mentions, is, of course, EdgeQL – an insanely good query language of EdgeDB.<p>The truth is EdgeQL is so good that you never want to go back to SQL ever after. It&#x27;s even a bit depressing when you realize how much time has been spent crafting SQL queries and dancing around it. EdgeQL renders most of those struggles obsolete.<p>The author of this post has written a book about SQL Window Functions, and probably developed an attachment with his SQL expertise. He probably doesn&#x27;t need another query language – nobody likes to return to the &quot;beginner&quot; level after their identity has been attached to the &quot;expert&quot; level.<p>But people who hadn&#x27;t developed abusive relationships with SQL expertise, they absolutely need &quot;your query language&quot;.

The biggest advantage of SQL is that it&#x27;s so common that if you deal with data a lot you tend to know it well enough. Sure, there are small differences between databases but joins&#x2F;grouping&#x2F;window functions tend to work similarly enough.<p>On the other hand, when I have to do a somewhat complex query in Elasticsearch, or MongoDB, or gorm, or Django ORM, I have to check each time in the docs how it&#x27;s done.

It&#x27;s funny how some devs are negative towards SQL but fiercely protective of other much more obscure tools from the 1970s, like unix utilities.

Nothing is perfect but given that SQL solves the problem, is ubiquitous, has tons of tooling and educational material, and is extremely mature... It&#x27;s not going anywhere.<p>It&#x27;s not a dinosaur, it&#x27;s a shark.<p>Would I like to have something more streamlined and less clunky? Absolutely. But it&#x27;s going to take a lot of effort for anything to become as ubiquitous as sql.

I felt the same way when Malloy[0] launched. It has some interesting features, but I couldn&#x27;t see myself ever using it. Nothing makes a big enough difference to spend the time to learn it.<p>Would love to hear from anybody that&#x27;s using it regularly<p>0 - <a href="https:&#x2F;&#x2F;www.malloydata.dev&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.malloydata.dev&#x2F;</a>

I would love if sql would support a slight syntax change of accepting<p>From table select col;<p>as an optional alternative to<p>select col from table;<p>This would allow autocompleting col names in editors.<p>Other than that I quite like sql being the standard db query language.

This honestly feels like a great advertisement for fancy-ql. Writing queries that take the form of the dataset you want back is awesome.

Query language for database queries! I thought the argument were going to be against application query languages, like the JQL for Jira and so on, which I actually like.

The ecosystem of tools and learning resources around SQL is so large that I think generally any FancyQL is a liability. It would need to bring a 10x improvement over SQL and that is hard to believe.<p><i>However</i>, I’d have said the same about JS a few years ago, and now we have TypeScript. Perhaps a language that is a strict superset of SQL and that compiles to SQL might be something worth trying.

&quot;You can write SQL queries in lower case&quot;

<i>sickos.jpg</i> YES!<p>I can&#x27;t count the number of services or things I&#x27;ve had to use which invent their own query language instead of using SQL. In every case the language is empirically worse than SQL, especially when it&#x27;s some mangled hybrid to make things &quot;easier&quot; <i>cough</i>NRQL<i>cough</i>. The silliest part is, if we all just fucking used SQL the tooling and integration would be much fucking easier and&#x2F;or free. Not to mention, in almost no organization will you have the time or resources to not make something that&#x27;s a half-implemented, poorly spec&#x27;d, rubbish version of SQL.<p>I think the biggest problem with SQL is that folks feel like they don&#x27;t need to know it or that it&#x27;s not useful. Oh dear beebs, it&#x27;s fucking useful. Next time I personally need to build a rich query interface, I&#x27;m just using row level security and opening up Postgres.

Lovely, right on the subject of all those wannabe replacements.

people who intentionally choose sql databases often make every effort to avoid writing any of the stuff by hand, which should say something about how much of the value proposition lies in the language itself<p>&gt; SQL has a solid standards committee that maintains and improves it.<p>so does c++. so does javascript. so does cobol

If this is about NoSQL databases, I dont think SQL is useful for databases which does not follow first normal form. But any alternative to SQL for relational databases will fight an uphill battle. While SQL is somewhat clunky, it is also deeply entrenched.

Datomic&#x27;s Datalog is much better but it&#x27;s not easy to unlearn SQL.

Perhaps I’m lucky, but I’ve never experienced SQL shaming. What I have experienced is referencing shaming, where I’m allowed to write SQL, but all table references in that SQL need to come from the model instead of being hard-coded in the SQL. I suppose it’s nice to have all the join tables’ models being included in the file. It makes it easy for a search to find all the usages in case there is a big refactor. It also makes the SQL look a lot more complicated then it really is and a lot less clean then these examples- at least in the code.

Actually SQL is pretty cool and by using concatenative concepts, you could do some pretty cool stuff.<p>We built a datascience tool to quickly build data apps which can be extended from the frontend, including data wrangling and datascience functions. Most exciting part was using the PostgreSQL and SQL to process, clean and enhance data and write extensions and bring it all together.<p>We open sourced alpha version yesterday, more documentation to come.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;rebataur&#x2F;rapidiam">https:&#x2F;&#x2F;github.com&#x2F;rebataur&#x2F;rapidiam</a>

I was exposed to Kusto Query Language this week. I used it to query logs in Azure. At first, I thought &quot;what? Another query language to learn? &quot; But I find myself liking it. It reminds me of ML style piping expressions, and it&#x27;s very explicitly lays out how each operator works on top of previous clauses, which makes it very clear how and <i>when</i> each clause works. I&#x27;m assuming &#x2F; hoping the queries that are actually executed are deferred - I would think they would have to be!

I agree with many points, however, it depends on the abstraction that you need and the abstraction depends on the architecture you are adopting.<p>For example, if you are doing DDD and your repository implementation is about SQL, adding another layer of abstraction is not worth. But if your design is less sophisticated, or you are in an early stage of the project, you may find appealing to use that abstraction.

Maybe I didn&#x27;t grok this article but all the examples made me think &quot;fancyql&quot; (as it calls it) looks a lot better than SQL.<p>If I could compile back and forth between SQL and &quot;fancyql&quot; then using fancyql feels like an absolute no brainer to me?<p>I&#x27;m a lot less sympathetic to the &quot;everyone already knows it&quot; argument after dealing with SQL queries that are many hundred lines long.

Why not both. If kibana supported sql that would be cool in addition to it’s 2 (or more) distinct syntaxes.<p>The issue is with non relational dbs like redshift that do support sql it is very easy to write an innocent query that takes hours to run if you don’t use specific keys in the query (ones used for sharding). But then some kind of warning or query plan indication would help there.

I feel like this entire debate is <i>strongly</i> influenced, if not soon made obsolete and pointless, by the presence of the so called AI tools.<p>Feels like there&#x27;s a universe of difference between the experience of &quot;carefully craft the query yourself&quot; and &quot;describe the query and let AI write the code for it.&quot;

But see <a href="https:&#x2F;&#x2F;www.scattered-thoughts.net&#x2F;writing&#x2F;against-sql&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.scattered-thoughts.net&#x2F;writing&#x2F;against-sql&#x2F;</a>

&gt; I don&#x27;t need your query language<p>With LLM&#x27;s, no one is ever going to need <i>any</i> query language starting effing now.<p>And good riddance to all of them too, I&#x27;ve yet to see one that made any kind of sense from the ease of use perspective.

This is like an airline startup offering you to fly on their much better, in-house designed&#x2F;built aircraft. I think it&#x27;s cool but no thank you, maybe I&#x27;ll check back in 10-20 years to see where they are.

Especially the last point makes me realize that many frustrations with SQL are transferred frustrations with the suits (from the 70s or not) that we&#x27;re all working for, and the company culture they&#x27;ve created

I feel exactly the same way about ActiveRecord ORMs. That’s why I created PluSQL:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;iaindooley&#x2F;PluSQL">https:&#x2F;&#x2F;github.com&#x2F;iaindooley&#x2F;PluSQL</a>

What happened to Datalog?

I used to bash SQL. Then I learned how to use it.

I wonder if the author has tried CodeQL? One of the best query languages I&#x27;ve ever used, even if it is very domain-specific.

How about nested selection ? SQL is dump because it doesn&#x27;t allow nested selection by default.

I&#x27;d also stick to SQL until my boss says we&#x27;ll be using something else because is cheaper.

In the lists of most popular languages, SQL always to seem up at the top.

SQL got the order of key words wrong. Instead of<p>„Select … from …“<p>it would be much better to write<p>„From … select …“.<p>PRQL got that right: <a href="https:&#x2F;&#x2F;prql-lang.org&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;prql-lang.org&#x2F;</a>