7 ms·

SQL is syntactic sugar for relational algebra

216 points by dmarto 2y ago

xbar 2y ago
Discussions of what is/is not syntactic sugar are unapproachable for me because I cannot get past the abuse of sugar's essential functions in the tortured metaphor.
roenxi 2y ago
I'm glad that the article concluded "No" to it's own headline. Calling SQL "syntactic sugar" is an insult to sugar. The "helpful diagram explaining how the scoping rules work" alone should make people blanch. The language is a syntactic disaster that we've been saddled with out of habit and inertia.
- Scene_Cast2 2y ago
  My take is that SQL is a bit like C - very nice to know, but move off of it to a more "managed" environment for safety and sanity.
  - williamdclt 2y ago
    Definitely disagree on this one. ORMs are a leaky abstraction, they hide the wall you’re going to bump into anyway, and they bring their own gotchas on top
    - Scene_Cast2 2y ago
      I worked with two frameworks so far - SQLalchemy core and a much bigger "orchestration" one (dagster / airflow equivalent). SQLalchemy got me type checking, column name checking, and DB migrations versioning with Alembic. The bigger framework got me more static analysis and dependency graphs on top of that. Those features saved me lots of time and headaches (type checking, dependency graphs, etc). Mind you, the frameworks I worked with still allowed for raw SQL for those edge cases that still pop up.
- solumunus 2y ago
  I agree that SQL syntax is terrible, but for some potentially masochistic reason I still like writing it. It could certainly be done better though and I don’t understand how there hasn’t been more innovation in this space.
  - IshKebab 2y ago
    There's PRQL which is really nice. But it has pretty big downsides: it only supports querying data, not modifying it; and since it's not database-specific it doesn't support all the features of whatever database you're using. E.g. it can't access complex data types like structs/arrays in DuckDB.
  - refset 2y ago
    > I don’t understand how there hasn’t been more innovation in this space I think it's simply that most businesses and investors don't register SQL as having any real problems, and especially now with a resurgent interest in SQL the idea of attempting anything novel feels too risky. Shameless plug of one recent attempt to offer something different: XTQL https://docs.xtdb.com/intro/what-is-xtql.html https://docs.xtdb.com/intro/what-is-xtql.html
    - account-5 2y ago
      Having been forced to write queries as JSON in elastic I definitely prefer writing SQL. I would actively avoid utilizing any query language where I have to count brackets.
      - refset 2y ago
        Handcrafting JSON is undoubtedly always a pain, but the idea with XTQL is rather that it can be easily generated from any regular programming language. > I would actively avoid utilizing any query language where I have to count brackets That's really an editor/tooling problem, solvable in many ways, but I guess a Python-like/Parinfer approach would be your preference? (where whitespace/indentation is significant)
- bugbuddy 2y ago
  Call a masochist but I love me good old SQL. I find most problems with systems I have worked with are the result of managements putting people who never paid attention in database classes in charge. Or maybe nobody really cares about doing a good job. In any case, my professors would have given all the legacy SQL databases that I have come across F-. The worse part is nobody dares changing anything because it would require application code changes. Don’t blame SQL. Blame incompetent people doing SQL badly.
  - b33j0r 2y ago
    I didn’t “get” SQL for the first 6-8 years of professional full-stack engineering. I was chasing, along with many in my cohort, the ORM abstraction dragon we thought we needed to work on j2ee, django, or sqlalchemy teams quickly. It’s perplexing to me now why SQL was perplexing. Even knowing about relational theory and enough DBA to pass interviews, it somehow always seemed like we should have been able to treat databases like our OOP’s. To think of the hours I wasted. Maybe I heard in a meeting “it’s the join table” and another colleague said “just use the association table,” and I was trying the n^2 debugging approach to solve it with model classes. The best advice I got on this made sense later: “I wouldn’t even try to manage that [dataset] myself. Just figure out how to tell a database to do it—that’s its only job.”
    - bitwize 2y ago
      Understanding relational algebra helps you write better SQL the way understanding lambda calculus helps you write better JavaScript. The language is warty and but a shadow of the pristine purity underneath, but it gets the job done and the theory guides your reasoning on how to use the language.
      - lkuty 2y ago
        In particular, I find the closure property of SQL very helpful and always think in terms of queries gettings tables and producing tables (derived most of the time). https://en.wikipedia.org/wiki/Closure_(mathematics) https://en.wikipedia.org/wiki/Closure_(mathematics)
- sherburt3 2y ago
  Strongly disagree, complete newbies can get up and running with SQL very quickly and it's expressive enough that experienced people can do things that no ORM would ever hope of being able to do. One criticism I have though is that whoever thought "SELECT" should come before "FROM" should be fired.
  - alexvoda 2y ago
    And yet I agree with both. SQL is at the same time easy to get started, incredibly powerful and yet also a complete disaster. Just like JavaScript but with standards you have to pay to read and implementers take their sweet time to implement. ORMs (with the possible exception of JOOQ, whose open source version is unfortunately also very limited) are hopelessly limited and have been a drag on the development of the field ever since.
    - swasheck 2y ago
      i think “disaster” is hyperbole. my brain thinks in concert with sql so it feels natural, though i will concede that the parsing/syntax order differences was an odd choice. programmers and language users all have a mental translation layers that they use to go from conceptual->concrete using the syntax and idioms of their language of choice. javascript doesn’t make sense in a lot of ways. calling it a disaster is hyperbole. go doesn’t make sense in a lot of ways. it’s not a disaster. unfamiliarity or disagreement with choices does not necessarily mean it’s a disaster the only language with which i’m familiar that is a complete, unmitigated disaster is the english language, but i suppose that’s outside of the scope of this conversation
      - runlaszlorun 2y ago
        > the only language with which i’m familiar that is a complete, unmitigated disaster is the english language Out of curiosity, are you a native speaker of english or did you learn it as a second language? I’m a native speaker and feel sorry for anyone who has to learn all its irregularities, etc. But the few times I’ve mentioned this to anyone who had to learn as a second language, they’d correct me and tell it was easy. Which baffled me. Obviously as a native speaker, I’m in no place to judge it.
- pkkm 2y ago
  Agreed, I wish that SQL was more concise and organized more left-to-right, like shell pipelines or the F# pipe operator. But what are the alternatives? The only one I know is PRQL, and that doesn't seem to be getting popular (correct me if I'm wrong).
  - AdrianB1 2y ago
    It is organized, especially if you write it correctly (split lines and indent). Minified, it is a nightmare, but with a proper layout it is a pleasure to read it. I just did a code review for a 20,000 LoC piece and it was quite a breeze after I improved the layout a bit.
    - pkkm 2y ago
      "Disaster" may be an exaggeration, but I think the syntax could be better, proper layout or not. In particular, I find that complex SQL queries often need to be read in a "spiral" order due to SQL's attempt to resemble natural language. I find PRQL's consistent left-to-right/top-to-bottom reading order a clear improvement.
  - __mharrison__ 2y ago
    Pandas and now Polars can replace a lot of SQL. I find that many of my students find these easier to build up and debug.
  - listenallyall 2y ago
    I can understand that the statement order can be awkward, such as choosing what to select before the source. But help me with "more concise". SQL has no boilerplate, its keywords are brief, you express the exact logic of the result set you're seeking. There are tons of SQL overlays or replacements embedded in programming languages or BI/reporting tools, and they are universally, more difficult to work with than straight SQL.
    - temporarely 2y ago
      There is a paper at the end of this link trail and that paper discusses everything that is wrong with SQL including concision. https://news.ycombinator.com/item?id=39798528 https://news.ycombinator.com/item?id=39798528 et voila: https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf see their comments on WITHIN GROUP and redundant declaration of identifiers. As for boiler plate, SQL's turf war over plain English words as reserved names creates this interesting situation of boiler plate identifiers in SQL to disambiguate from reserved keywords.
- fudged71 2y ago
  https://en.wikipedia.org/wiki/Betteridge%27s_law_of_headlines https://en.wikipedia.org/wiki/Betteridge%27s_law_of_headline...
- jameshart 2y ago
  Syntactic sugar, in the sense that it is a syntax that is bad for you.
KingOfCoders 2y ago
Everything is just syntactic sugar for something else. I'm syntactic sugar for the hydrogen atoms in my body.
- WWWWH 2y ago
  And on some cases sugar sugar not syntactic sugar.
- lkey 2y ago
  Your comment is both needlessly dismissive, and worse, incorrect. You cannot get a human being, much less the person that is you, by recursively applying a constant set of rewrite rules on unstructured hydrogen atoms. If you think to counter my assertion with "The standard model of particle physics and the big bang already did that, I'm here after all", then spare us both the trouble and don't reply. The particular arrangement of all known matter and energy in the universe at t=0 is not a repeatable initial condition. Some rewriting systems are in fact Turing complete[1], and that's an interesting digression. However, it's far afield from the article's discussion of untangling the syntactic mess that is the SQL standard and bringing it closer in line with the standard expression of its semantics. [1]: https://www.sciencedirect.com/science/article/pii/0304397592900228 https://www.sciencedirect.com/science/article/pii/0304397592...
  - KingOfCoders 2y ago
    "constant set of rewrite rules" We just don't know the rewrite rules. And I didn't say unstructured. And you need Carbon atoms etc. - hydrogen was just a shortcut.
halayli 2y ago
Relational algebra IR is implemented in MonetDB and discussed in their paper. Definitely worth reading. Not trying to be picky but pure relational algebra doesn't map to SQL and IMO it's not a good idea to attempt to do that due to the fact that relational algebra treats tuples as mathematical sets (ordering/uniqueness matters) while SQL does not(and has to deal with nullability).
infogulch 2y ago
SQL is pretty good all things considered. But I've always looked out for languages that can represent relational algebra concepts more directly. Maybe CozoaDB is close, though still immature. Any recommendations?
- markisus 2y ago
  I’ve been using Pandas which exposes a python slicing syntax for manipulating relational data. It also has a builtin join() function. “select id, date from orders” is orders[“id”, “date”]. It’s meant for in-memory datasets but the syntax could be extended to work for other backends. I’m not sure if anyone is working on that.
  - __mharrison__ 2y ago
    Ibis takes the notion of a dataframe and abstracts it from SQL backends.
- samatman 2y ago
  I highly recommend the Third Manifesto. I could link this under most posts in this thread but I'll limit myself to two. https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf The only problem there is that you might want to use a D language, and well. You can't. There was a product called Dataphor which one can find some writeups on but, baffling though I find this, there are no robust open-source relational databases which use a D language.
triska 2y ago
Codd's seminal paper, A Relational Model of Data for Large Shared Data Banks, states that a language based on applied predicate calculus "would provide a yard-stick of linguistic power for all other proposed data languages". Quoting from https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf: "1.5 Some linguistic aspects The adoption of a relational model of data, as described above, permits the development of a universal data sub-language based on an applied predicate calculus. A first-order predicate calculus suffices if the collection of relations is in normal form. Such a language would provide a yard-stick of linguistic power for all other proposed data languages, and would itself be a strong candidate for embedding (with appropriate syntactic modification) in a variety of host languages (programming, command- or problem-oriented)." Languages based on predicate calculus indeed seem extremely suitable for reasoning about relational data. Datalog is a well-known example. It is more directly based on predicate logic, and much simpler than SQL.
refset 2y ago
> Lest you think is just one weird corner of the sql spec, I found this helpful diagram explaining how the scoping rules work (from Neumann and Leis, 2023) It's an excellent diagram, it really conveys the dissonance. Incidentally I interviewed Viktor Leis on a podcast last week about the paper where it's from: https://juxt.pro/blog/sane-query-languages-podcast/ https://juxt.pro/blog/sane-query-languages-podcast/ A lot of people seem to believe that LLMs or other ML methods can overcome the complexity challenges of generating SQL accurately, but I'm yet to be convinced that a database-powered AI revolution can happen without somehow bypassing SQL.
joking 2y ago
A few tweaks here and there and it would be nice enough for me. Most of them are actually implemented by some engines but are not part of the standard. Just changing the order of the from and select clauses so autocomplete can know what fields can you use would be a nice enough change.
samsquire 2y ago
Thanks for this interesting post. Intuitively, relational algebra compresses enumeration over data in time that a CPU executing billions of cycles a second can feasibly and efficiently traverse and execute against many collections of millions or billions of records in human perceivable time thanks to indexes. I've been trying to think of systems communicating with eachother as parts of a relational model in the sense we can model system behaviour as a series of events and a join is a communication between components. I would love to talk about this with people.
- refset 2y ago
  > a join is a communication between components Makes me wonder just how far people have pushed Foreign Data Wrappers in practice.
- seanhunter 2y ago
  Some years back I spent a weekend and built a "good enough to prove to myself" version of this in Haskell that implemented the main relational operations of projection, selection and cartesian product. The basic idea was based on the "stream fusion" papers. So the relation was a stream in the stream fusion sense so it was pretty trivial to implement the normal relational operators in that paradigm. Changing this type of system to work on "events" as input would be pretty trivial. The one thing I never managed to get to work was the actual "fusion" compiler hint. I kept trying variants of what he did in the paper but ghc just refused to optimise my stream/unsteam unstream/stream pairs away because it had already done some rewriting to them. I couldn't figure out how to apply the optimisation early enough to be effective. [1] Which are a fantastic read if you're into CS whatever you think of my idea https://www.cs.tufts.edu/~nr/cs257/archive/duncan-coutts/stream-fusion.pdf https://www.cs.tufts.edu/~nr/cs257/archive/duncan-coutts/str... and https://www.researchgate.net/publication/220802863_Rewriting_Haskell_Strings https://www.researchgate.net/publication/220802863_Rewriting...
  - samsquire 2y ago
    Thank you for sharing your thoughts and ideas Sean, appreciated. I am unfamiliar with the source material and I have recorded the paper you linked to my reading list. From a description of "stream fusion", it reminds me of Clojure's "transducers". It also reminds me of Kafka's Table/Stream duality. Term rewriting is something is really interesting to me. Communication, protocols and Communicating sequential processes, session types are all ideas I am thinking about and trying to understand.
bkanuka 2y ago
As someone who learned mathematics first and programming later, I think it took me about 10 years of working in data-intensive programming before I could write really "good" SQL from scratch. I completely attribute this to SQL being difficult or "backwards" to parse. I mean backwards in the way that in SQL you start with what you want first (the SELECT) rather than what you have and widdling it down. Also in SQL (as the author states) you often need to read and understand the structure of the database before you can be 100% sure what the query is doing. SQL is very difficult to parse into a consistent symbolic language. The turning point for me was to just accept SQL for what it is. It feels overly flexible in some areas (and then comparatively ridgid in other areas), but instead of fighting against this or trying to understand it as a consistent, precise language , I instead just go "oh SQL - you are not like the other programming languages I use but you can do some pretty neat stuff so we can be on good terms". Writing good SQL involves understanding the database, understanding exactly the end result you want, and only then constructing the subqueries or building blocks you need to get to your result. (then followed by some trial and error of course)
- dmead 2y ago
  Saying what you want first rather than what you have is evidence of the von Neumann bottleneck or it was a sign of the times when SQL was being developed on 1970s machine. Either way, point taken that it is not like a proof.
  - ako 2y ago
    Covey’s: “start with the end in mind” is not a bad advise when building something complex. With procedural languages you do the same, you first write the signature, parameters expected to go in and out, and then you start writing the way to achieve this.
- mrits 2y ago
  I always thought writing SQL from scratch was the easy part. The hard part for me was coming back to my query a few weeks later
  - 2y ago
zvmaz 2y ago
I tried to study C. J. Date's books to understand relational theory... suffice it to say that I got nothing from his books, except a deep irritation partly due to his absolute pedantry... I finally learned SQL with a gentle introduction by Alan Beaulieu. I stumbled upon another book that's about the theory: Applied Mathematics for Database Professionals, by Lex deHaan, and Toon Koppelaars. Maybe these authors will benevolently teach me relational theory. But please avoid C. J. Date's books. And don't be him when writing a book or trying to explain something to another human being.
exabrial 2y ago
I’d much rather deal with the peculiarities of SQL than any of the attempted replacements (ones I’ve seen in my minted experience). Elastic for instance, other json based languages, are absolutely terrible. We lost something we when stopped writing ANSI standards. We’ve even stayed on InfluxDB og versions _because of _ the SQL like syntax, and also their improved languages are a nuclear disaster area. SQL, despite its flaws (null != null) is pretty good enough!
keid 2y ago
See C.J. Date's "An Introduction to Database Systems," https://www.amazon.com/Introduction-Database-Systems-8th/dp/0321197844 https://www.amazon.com/Introduction-Database-Systems-8th/dp/... This is not news.
scythmic_waves 2y ago
This is a great write up. There appear to be a few camps forming in the comments and I’m in camp “SQL is confusing and attempts to explain it in terms of relational algebra have felt inadequate to me”. It also gives me some good follow up material to read. I’m particularly interested in that one link that forms subqueries and lateral joins in terms of a new “dependent join” operator.
- zer00eyz 2y ago
  Go read: Database Design for Mere Mortals. ERD's are your friend. Learn how to generate one, and how to read it. The relations (not relational, not algebra) are IN the design they are IN the ERD (as a tool to visualize). Even if your not visual thinker the ERD might help you find a path between two distant tables. Needing a subquery is rare. It happens but a lot of subqueries would be better off as joins. The moment you grasp the design of something you're less likely to want to sub query. Explain is your friend. Reading an explain plan is going to give you some good insight into what is going on UNDER the hood. Not only will it help you tune slow queries but it is more insight into how large queries decompose. Lastly, there is nothing worse than having to query a badly designed DB. If you do a shit job on the first part everything else is going to be painful.
achr2 2y ago
You should look at LINQ in C#/.net . The SQL-like syntax always has a function-first equivalent, that gets across this point fairly eloquently.
qazxcvbnm 2y ago
As someone who has implemented a composable SQL generator from user-defined algebras of (arbitrary SQL) queries using relational algebra, I understand the shortcomings of SQL when viewed from an angle of a relational query language. However, SQL is a language with many facets (DML, DDL, DCL) other than 'relational' querying. Putting on a less mathematical and more engineering mindset, SQL ingratiates me by its interface to incredibly powerful primitives difficult to find anywhere else. (I've primarily worked with Postgres SQL) Consider the humble function; in SQL https://www.postgresql.org/docs/current/sql-createfunction.html https://www.postgresql.org/docs/current/sql-createfunction.h..., one can declare the function as `stable` or `immutable` to let the runtime optimise repeated calls; as `parallel` to let the runtime consider parallelisation, as `cost ...` and `rows ...` to aid optimiser cost estimation. Imagine if one could do that in C or Javascript! Another facet which regularly puts me in awe is the transaction isolation primitives and locking primitives offered by SQL. I understand that as a database language, SQL necessarily has these within its specialised niche, but it seems to me these aspects of SQL as an interface to a language runtime would be equally useful in the everyday program; in all these areas of functionality, SQL is so much more advanced than nearly every other general purpose programming language.
barfbagginus 2y ago
Can we call it syntactic ashtray? Because it feels like I'm sucking on 1970s ashtray when I see or use it. Those who have read their Spivak 2017 will know that databases are just Co-presheaves of Ologs over the Kliesli Category of the Power-Set Monad, the Identity Monad, or the Giry Monad. I would like a QL that acts like it!
- breezeTrowel 2y ago
  I know some of these words.
  - lkey 2y ago
    Snark of grandparent aside: https://arxiv.org/abs/1102.1889 https://arxiv.org/abs/1102.1889 if you want to read more.
jameshart 2y ago
Not totally convinced by the ORDER BY obstacles that the author raises.. table('test').project('a').orderBy('b') > That's an error, because we can't order by a column that we just projected away. Right? assumes that 'projection' completely eliminates part of the underlying relation, but why does that have to be the case? If a relation includes 'selected fields' and 'hidden fields', and project just 'hides' the fields it doesn't project, while orderBy can operate on either projected or hidden fields, this ends up being perfectly sound. Even the more complex example which is translated as follows: translate('select a+1 as c from test order by b,c') => table('test').project('a','b').addColumn('a+1', as='c').orderBy('b','c').project('a') would work fine as: table('test') // selected: [a, b, ...], hidden: [] .addColumn('a+1', as='c') // selected: [a, b, c, ...], hidden: [] .project('c') // selected: [c], hidden: [a, b, ...] .orderBy('b','c') // selected: [c], hidden: [a, b, ...] (not sure why there's a .project('a') on the end of their version) Which is a reasonably local, algebraic transformation.
samatman 2y ago
If you're interested in what it would take to put relational databases back on the rigorous footing of relational algebras, the Third Manifesto is a good place to start. https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf I find it somewhat sad that an implementation of a database with a proper D language hasn't broken out and become a ubiquitous tool for the profession. There were some proprietary versions shortly after the manifesto's publication, but it never caught on.
aoeusnth1 2y ago
I find that most people who object to SQL do not use TVFs. If you don’t have any tools to easily break down the steps of the work, of course SQL will feel like an opaque Write-only language. With TVFs you can easily iteratively add more complex steps to your query while checking your work while you build.
- lbourdages 2y ago
  What does TVF mean? I have been able to find anything on Google, all I get is an Indian streaming service... "Truth value function"?
  - housecarpenter 2y ago
    Table-valued function.
r00fus 2y ago
That diagram separating the syntactic vs. semantic layers of a SQL statement (from Neumann & Leis paper) is brilliant.
chubot 2y ago
An analogy I like is - Are Perl-style regexes (used in Python, Ruby, Java, .NET, etc.) syntactic sugar for regular languages? The answer is no, because Perl added all sorts of imperative doodads to regexes, which can’t be easily represented and executed in the automata-based paradigm. Trying to do this is like a “research paper generator” (and not in a bad way), e.g. Derivative Based Nonbacktracking Real-World Regex Matching with Backtracking Semantics - https://dl.acm.org/doi/abs/10.1145/3591262 https://dl.acm.org/doi/abs/10.1145/3591262 (2023) This is until Go and Rust, which used automata-based regexes from the beginning. I don’t think users have lost much. Purely automata-based engines are kind of pleasant to write, because almost everything is in the compiler, and not in the runtime, e.g. https://github.com/andychu/rsc-regexp/blob/master/py/README.md https://github.com/andychu/rsc-regexp/blob/master/py/README.... That is, features like ? + * really are syntactic sugar for repetition. There’s also a lot of syntax sugar around character classes like [^a], and the runtime is very small. --- Likewise, SQL seems to have so many non-relational doodads in its language design, which cause problems for implementers. In this case, I think there’s an incentive problem with SQL: It benefits vendors if their dialect is harder to re-implement. Although certainly they’ve added many useful features too in 4-5 decades! To me a language design issue is we never really “learned” to compose languages with different paradigms: - the set-based paradigms like relational algebra and regular languages, with - Turing-machine like code. (and also I/O!) We never learned polyglot programming, so each language becomes its own source of “reckless growth” – its own parochial backwater. Both regexes and SQL should be able to “escape” to normal code, and that would greatly simplify them. This can be done both by language implementers and by application programmers, i.e. “factoring” across languages. It’s not always obvious how to do this, but it certainly it can be done more than we do it today. --- I’d argue the same phenomenon – lack of language composition – leads to programming languages within YAML. Github Actions is nominally some kind of “declarative” scheduler specification, or graph (job -> job dependencies), but that’s not enough for many problems. So it also has a bunch of doodads for escaping that model (to the extent it has a model). Shell, Awk, and Make also grew many doodads (https://www.oilshell.org/blog/2016/11/14.html https://www.oilshell.org/blog/2016/11/14.html), which are not very well designed. They used to be declarative languages, but no longer are. Although there is some distinction between “formerly set-based languages” like SQL and regex, and other “declarative” non-Turing-complete languages. But I think the language composition problem is approximately the same. Part of it is syntax, but a lot of it is semantics. (copy of lobste.rs comment)

216 points by dmarto 2y ago

xbar 2y ago

Discussions of what is/is not syntactic sugar are unapproachable for me because I cannot get past the abuse of sugar's essential functions in the tortured metaphor.

roenxi 2y ago

I'm glad that the article concluded "No" to it's own headline. Calling SQL "syntactic sugar" is an insult to sugar. The "helpful diagram explaining how the scoping rules work" alone should make people blanch. The language is a syntactic disaster that we've been saddled with out of habit and inertia.

Scene_Cast2 2y ago
My take is that SQL is a bit like C - very nice to know, but move off of it to a more "managed" environment for safety and sanity.
- williamdclt 2y ago
  Definitely disagree on this one. ORMs are a leaky abstraction, they hide the wall you’re going to bump into anyway, and they bring their own gotchas on top
  - Scene_Cast2 2y ago
    I worked with two frameworks so far - SQLalchemy core and a much bigger "orchestration" one (dagster / airflow equivalent). SQLalchemy got me type checking, column name checking, and DB migrations versioning with Alembic. The bigger framework got me more static analysis and dependency graphs on top of that. Those features saved me lots of time and headaches (type checking, dependency graphs, etc). Mind you, the frameworks I worked with still allowed for raw SQL for those edge cases that still pop up.
solumunus 2y ago
I agree that SQL syntax is terrible, but for some potentially masochistic reason I still like writing it. It could certainly be done better though and I don’t understand how there hasn’t been more innovation in this space.
- IshKebab 2y ago
  There's PRQL which is really nice. But it has pretty big downsides: it only supports querying data, not modifying it; and since it's not database-specific it doesn't support all the features of whatever database you're using. E.g. it can't access complex data types like structs/arrays in DuckDB.
- refset 2y ago
  > I don’t understand how there hasn’t been more innovation in this space I think it's simply that most businesses and investors don't register SQL as having any real problems, and especially now with a resurgent interest in SQL the idea of attempting anything novel feels too risky. Shameless plug of one recent attempt to offer something different: XTQL https://docs.xtdb.com/intro/what-is-xtql.html https://docs.xtdb.com/intro/what-is-xtql.html
  - account-5 2y ago
    Having been forced to write queries as JSON in elastic I definitely prefer writing SQL. I would actively avoid utilizing any query language where I have to count brackets.
    - refset 2y ago
      Handcrafting JSON is undoubtedly always a pain, but the idea with XTQL is rather that it can be easily generated from any regular programming language. > I would actively avoid utilizing any query language where I have to count brackets That's really an editor/tooling problem, solvable in many ways, but I guess a Python-like/Parinfer approach would be your preference? (where whitespace/indentation is significant)
bugbuddy 2y ago
Call a masochist but I love me good old SQL. I find most problems with systems I have worked with are the result of managements putting people who never paid attention in database classes in charge. Or maybe nobody really cares about doing a good job. In any case, my professors would have given all the legacy SQL databases that I have come across F-. The worse part is nobody dares changing anything because it would require application code changes. Don’t blame SQL. Blame incompetent people doing SQL badly.
- b33j0r 2y ago
  I didn’t “get” SQL for the first 6-8 years of professional full-stack engineering. I was chasing, along with many in my cohort, the ORM abstraction dragon we thought we needed to work on j2ee, django, or sqlalchemy teams quickly. It’s perplexing to me now why SQL was perplexing. Even knowing about relational theory and enough DBA to pass interviews, it somehow always seemed like we should have been able to treat databases like our OOP’s. To think of the hours I wasted. Maybe I heard in a meeting “it’s the join table” and another colleague said “just use the association table,” and I was trying the n^2 debugging approach to solve it with model classes. The best advice I got on this made sense later: “I wouldn’t even try to manage that [dataset] myself. Just figure out how to tell a database to do it—that’s its only job.”
  - bitwize 2y ago
    Understanding relational algebra helps you write better SQL the way understanding lambda calculus helps you write better JavaScript. The language is warty and but a shadow of the pristine purity underneath, but it gets the job done and the theory guides your reasoning on how to use the language.
    - lkuty 2y ago
      In particular, I find the closure property of SQL very helpful and always think in terms of queries gettings tables and producing tables (derived most of the time). https://en.wikipedia.org/wiki/Closure_(mathematics) https://en.wikipedia.org/wiki/Closure_(mathematics)
sherburt3 2y ago
Strongly disagree, complete newbies can get up and running with SQL very quickly and it's expressive enough that experienced people can do things that no ORM would ever hope of being able to do. One criticism I have though is that whoever thought "SELECT" should come before "FROM" should be fired.
- alexvoda 2y ago
  And yet I agree with both. SQL is at the same time easy to get started, incredibly powerful and yet also a complete disaster. Just like JavaScript but with standards you have to pay to read and implementers take their sweet time to implement. ORMs (with the possible exception of JOOQ, whose open source version is unfortunately also very limited) are hopelessly limited and have been a drag on the development of the field ever since.
  - swasheck 2y ago
    i think “disaster” is hyperbole. my brain thinks in concert with sql so it feels natural, though i will concede that the parsing/syntax order differences was an odd choice. programmers and language users all have a mental translation layers that they use to go from conceptual->concrete using the syntax and idioms of their language of choice. javascript doesn’t make sense in a lot of ways. calling it a disaster is hyperbole. go doesn’t make sense in a lot of ways. it’s not a disaster. unfamiliarity or disagreement with choices does not necessarily mean it’s a disaster the only language with which i’m familiar that is a complete, unmitigated disaster is the english language, but i suppose that’s outside of the scope of this conversation
    - runlaszlorun 2y ago
      > the only language with which i’m familiar that is a complete, unmitigated disaster is the english language Out of curiosity, are you a native speaker of english or did you learn it as a second language? I’m a native speaker and feel sorry for anyone who has to learn all its irregularities, etc. But the few times I’ve mentioned this to anyone who had to learn as a second language, they’d correct me and tell it was easy. Which baffled me. Obviously as a native speaker, I’m in no place to judge it.
pkkm 2y ago
Agreed, I wish that SQL was more concise and organized more left-to-right, like shell pipelines or the F# pipe operator. But what are the alternatives? The only one I know is PRQL, and that doesn't seem to be getting popular (correct me if I'm wrong).
- AdrianB1 2y ago
  It is organized, especially if you write it correctly (split lines and indent). Minified, it is a nightmare, but with a proper layout it is a pleasure to read it. I just did a code review for a 20,000 LoC piece and it was quite a breeze after I improved the layout a bit.
  - pkkm 2y ago
    "Disaster" may be an exaggeration, but I think the syntax could be better, proper layout or not. In particular, I find that complex SQL queries often need to be read in a "spiral" order due to SQL's attempt to resemble natural language. I find PRQL's consistent left-to-right/top-to-bottom reading order a clear improvement.
- __mharrison__ 2y ago
  Pandas and now Polars can replace a lot of SQL. I find that many of my students find these easier to build up and debug.
- listenallyall 2y ago
  I can understand that the statement order can be awkward, such as choosing what to select before the source. But help me with "more concise". SQL has no boilerplate, its keywords are brief, you express the exact logic of the result set you're seeking. There are tons of SQL overlays or replacements embedded in programming languages or BI/reporting tools, and they are universally, more difficult to work with than straight SQL.
  - temporarely 2y ago
    There is a paper at the end of this link trail and that paper discusses everything that is wrong with SQL including concision. https://news.ycombinator.com/item?id=39798528 https://news.ycombinator.com/item?id=39798528 et voila: https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf see their comments on WITHIN GROUP and redundant declaration of identifiers. As for boiler plate, SQL's turf war over plain English words as reserved names creates this interesting situation of boiler plate identifiers in SQL to disambiguate from reserved keywords.
fudged71 2y ago
https://en.wikipedia.org/wiki/Betteridge%27s_law_of_headlines https://en.wikipedia.org/wiki/Betteridge%27s_law_of_headline...
jameshart 2y ago
Syntactic sugar, in the sense that it is a syntax that is bad for you.

KingOfCoders 2y ago

Everything is just syntactic sugar for something else. I'm syntactic sugar for the hydrogen atoms in my body.

WWWWH 2y ago
And on some cases sugar sugar not syntactic sugar.
lkey 2y ago
Your comment is both needlessly dismissive, and worse, incorrect. You cannot get a human being, much less the person that is you, by recursively applying a constant set of rewrite rules on unstructured hydrogen atoms. If you think to counter my assertion with "The standard model of particle physics and the big bang already did that, I'm here after all", then spare us both the trouble and don't reply. The particular arrangement of all known matter and energy in the universe at t=0 is not a repeatable initial condition. Some rewriting systems are in fact Turing complete[1], and that's an interesting digression. However, it's far afield from the article's discussion of untangling the syntactic mess that is the SQL standard and bringing it closer in line with the standard expression of its semantics. [1]: https://www.sciencedirect.com/science/article/pii/0304397592900228 https://www.sciencedirect.com/science/article/pii/0304397592...
- KingOfCoders 2y ago
  "constant set of rewrite rules" We just don't know the rewrite rules. And I didn't say unstructured. And you need Carbon atoms etc. - hydrogen was just a shortcut.

halayli 2y ago

Relational algebra IR is implemented in MonetDB and discussed in their paper. Definitely worth reading. Not trying to be picky but pure relational algebra doesn't map to SQL and IMO it's not a good idea to attempt to do that due to the fact that relational algebra treats tuples as mathematical sets (ordering/uniqueness matters) while SQL does not(and has to deal with nullability).

infogulch 2y ago

SQL is pretty good all things considered. But I've always looked out for languages that can represent relational algebra concepts more directly. Maybe CozoaDB is close, though still immature. Any recommendations?

markisus 2y ago
I’ve been using Pandas which exposes a python slicing syntax for manipulating relational data. It also has a builtin join() function. “select id, date from orders” is orders[“id”, “date”]. It’s meant for in-memory datasets but the syntax could be extended to work for other backends. I’m not sure if anyone is working on that.
- __mharrison__ 2y ago
  Ibis takes the notion of a dataframe and abstracts it from SQL backends.
samatman 2y ago
I highly recommend the Third Manifesto. I could link this under most posts in this thread but I'll limit myself to two. https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf The only problem there is that you might want to use a D language, and well. You can't. There was a product called Dataphor which one can find some writeups on but, baffling though I find this, there are no robust open-source relational databases which use a D language.

triska 2y ago

Codd's seminal paper, A Relational Model of Data for Large Shared Data Banks, states that a language based on applied predicate calculus "would provide a yard-stick of linguistic power for all other proposed data languages". Quoting from https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf: "1.5 Some linguistic aspects The adoption of a relational model of data, as described above, permits the development of a universal data sub-language based on an applied predicate calculus. A first-order predicate calculus suffices if the collection of relations is in normal form. Such a language would provide a yard-stick of linguistic power for all other proposed data languages, and would itself be a strong candidate for embedding (with appropriate syntactic modification) in a variety of host languages (programming, command- or problem-oriented)." Languages based on predicate calculus indeed seem extremely suitable for reasoning about relational data. Datalog is a well-known example. It is more directly based on predicate logic, and much simpler than SQL.

refset 2y ago

> Lest you think is just one weird corner of the sql spec, I found this helpful diagram explaining how the scoping rules work (from Neumann and Leis, 2023) It's an excellent diagram, it really conveys the dissonance. Incidentally I interviewed Viktor Leis on a podcast last week about the paper where it's from: https://juxt.pro/blog/sane-query-languages-podcast/ https://juxt.pro/blog/sane-query-languages-podcast/ A lot of people seem to believe that LLMs or other ML methods can overcome the complexity challenges of generating SQL accurately, but I'm yet to be convinced that a database-powered AI revolution can happen without somehow bypassing SQL.

joking 2y ago

A few tweaks here and there and it would be nice enough for me. Most of them are actually implemented by some engines but are not part of the standard. Just changing the order of the from and select clauses so autocomplete can know what fields can you use would be a nice enough change.

samsquire 2y ago

Thanks for this interesting post. Intuitively, relational algebra compresses enumeration over data in time that a CPU executing billions of cycles a second can feasibly and efficiently traverse and execute against many collections of millions or billions of records in human perceivable time thanks to indexes. I've been trying to think of systems communicating with eachother as parts of a relational model in the sense we can model system behaviour as a series of events and a join is a communication between components. I would love to talk about this with people.

refset 2y ago
> a join is a communication between components Makes me wonder just how far people have pushed Foreign Data Wrappers in practice.
seanhunter 2y ago
Some years back I spent a weekend and built a "good enough to prove to myself" version of this in Haskell that implemented the main relational operations of projection, selection and cartesian product. The basic idea was based on the "stream fusion" papers. So the relation was a stream in the stream fusion sense so it was pretty trivial to implement the normal relational operators in that paradigm. Changing this type of system to work on "events" as input would be pretty trivial. The one thing I never managed to get to work was the actual "fusion" compiler hint. I kept trying variants of what he did in the paper but ghc just refused to optimise my stream/unsteam unstream/stream pairs away because it had already done some rewriting to them. I couldn't figure out how to apply the optimisation early enough to be effective. [1] Which are a fantastic read if you're into CS whatever you think of my idea https://www.cs.tufts.edu/~nr/cs257/archive/duncan-coutts/stream-fusion.pdf https://www.cs.tufts.edu/~nr/cs257/archive/duncan-coutts/str... and https://www.researchgate.net/publication/220802863_Rewriting_Haskell_Strings https://www.researchgate.net/publication/220802863_Rewriting...
- samsquire 2y ago
  Thank you for sharing your thoughts and ideas Sean, appreciated. I am unfamiliar with the source material and I have recorded the paper you linked to my reading list. From a description of "stream fusion", it reminds me of Clojure's "transducers". It also reminds me of Kafka's Table/Stream duality. Term rewriting is something is really interesting to me. Communication, protocols and Communicating sequential processes, session types are all ideas I am thinking about and trying to understand.

bkanuka 2y ago

As someone who learned mathematics first and programming later, I think it took me about 10 years of working in data-intensive programming before I could write really "good" SQL from scratch. I completely attribute this to SQL being difficult or "backwards" to parse. I mean backwards in the way that in SQL you start with what you want first (the SELECT) rather than what you have and widdling it down. Also in SQL (as the author states) you often need to read and understand the structure of the database before you can be 100% sure what the query is doing. SQL is very difficult to parse into a consistent symbolic language. The turning point for me was to just accept SQL for what it is. It feels overly flexible in some areas (and then comparatively ridgid in other areas), but instead of fighting against this or trying to understand it as a consistent, precise language , I instead just go "oh SQL - you are not like the other programming languages I use but you can do some pretty neat stuff so we can be on good terms". Writing good SQL involves understanding the database, understanding exactly the end result you want, and only then constructing the subqueries or building blocks you need to get to your result. (then followed by some trial and error of course)

dmead 2y ago
Saying what you want first rather than what you have is evidence of the von Neumann bottleneck or it was a sign of the times when SQL was being developed on 1970s machine. Either way, point taken that it is not like a proof.
- ako 2y ago
  Covey’s: “start with the end in mind” is not a bad advise when building something complex. With procedural languages you do the same, you first write the signature, parameters expected to go in and out, and then you start writing the way to achieve this.
mrits 2y ago
I always thought writing SQL from scratch was the easy part. The hard part for me was coming back to my query a few weeks later
- 2y ago

zvmaz 2y ago

I tried to study C. J. Date's books to understand relational theory... suffice it to say that I got nothing from his books, except a deep irritation partly due to his absolute pedantry... I finally learned SQL with a gentle introduction by Alan Beaulieu. I stumbled upon another book that's about the theory: Applied Mathematics for Database Professionals, by Lex deHaan, and Toon Koppelaars. Maybe these authors will benevolently teach me relational theory. But please avoid C. J. Date's books. And don't be him when writing a book or trying to explain something to another human being.

exabrial 2y ago

I’d much rather deal with the peculiarities of SQL than any of the attempted replacements (ones I’ve seen in my minted experience). Elastic for instance, other json based languages, are absolutely terrible. We lost something we when stopped writing ANSI standards. We’ve even stayed on InfluxDB og versions _because of _ the SQL like syntax, and also their improved languages are a nuclear disaster area. SQL, despite its flaws (null != null) is pretty good enough!

keid 2y ago

See C.J. Date's "An Introduction to Database Systems," https://www.amazon.com/Introduction-Database-Systems-8th/dp/0321197844 https://www.amazon.com/Introduction-Database-Systems-8th/dp/... This is not news.

scythmic_waves 2y ago

This is a great write up. There appear to be a few camps forming in the comments and I’m in camp “SQL is confusing and attempts to explain it in terms of relational algebra have felt inadequate to me”. It also gives me some good follow up material to read. I’m particularly interested in that one link that forms subqueries and lateral joins in terms of a new “dependent join” operator.

zer00eyz 2y ago
Go read: Database Design for Mere Mortals. ERD's are your friend. Learn how to generate one, and how to read it. The relations (not relational, not algebra) are IN the design they are IN the ERD (as a tool to visualize). Even if your not visual thinker the ERD might help you find a path between two distant tables. Needing a subquery is rare. It happens but a lot of subqueries would be better off as joins. The moment you grasp the design of something you're less likely to want to sub query. Explain is your friend. Reading an explain plan is going to give you some good insight into what is going on UNDER the hood. Not only will it help you tune slow queries but it is more insight into how large queries decompose. Lastly, there is nothing worse than having to query a badly designed DB. If you do a shit job on the first part everything else is going to be painful.

achr2 2y ago

You should look at LINQ in C#/.net . The SQL-like syntax always has a function-first equivalent, that gets across this point fairly eloquently.

qazxcvbnm 2y ago

As someone who has implemented a composable SQL generator from user-defined algebras of (arbitrary SQL) queries using relational algebra, I understand the shortcomings of SQL when viewed from an angle of a relational query language. However, SQL is a language with many facets (DML, DDL, DCL) other than 'relational' querying. Putting on a less mathematical and more engineering mindset, SQL ingratiates me by its interface to incredibly powerful primitives difficult to find anywhere else. (I've primarily worked with Postgres SQL) Consider the humble function; in SQL https://www.postgresql.org/docs/current/sql-createfunction.html https://www.postgresql.org/docs/current/sql-createfunction.h..., one can declare the function as `stable` or `immutable` to let the runtime optimise repeated calls; as `parallel` to let the runtime consider parallelisation, as `cost ...` and `rows ...` to aid optimiser cost estimation. Imagine if one could do that in C or Javascript! Another facet which regularly puts me in awe is the transaction isolation primitives and locking primitives offered by SQL. I understand that as a database language, SQL necessarily has these within its specialised niche, but it seems to me these aspects of SQL as an interface to a language runtime would be equally useful in the everyday program; in all these areas of functionality, SQL is so much more advanced than nearly every other general purpose programming language.

barfbagginus 2y ago

Can we call it syntactic ashtray? Because it feels like I'm sucking on 1970s ashtray when I see or use it. Those who have read their Spivak 2017 will know that databases are just Co-presheaves of Ologs over the Kliesli Category of the Power-Set Monad, the Identity Monad, or the Giry Monad. I would like a QL that acts like it!

breezeTrowel 2y ago
I know some of these words.
- lkey 2y ago
  Snark of grandparent aside: https://arxiv.org/abs/1102.1889 https://arxiv.org/abs/1102.1889 if you want to read more.

jameshart 2y ago

Not totally convinced by the ORDER BY obstacles that the author raises.. table('test').project('a').orderBy('b') > That's an error, because we can't order by a column that we just projected away. Right? assumes that 'projection' completely eliminates part of the underlying relation, but why does that have to be the case? If a relation includes 'selected fields' and 'hidden fields', and project just 'hides' the fields it doesn't project, while orderBy can operate on either projected or hidden fields, this ends up being perfectly sound. Even the more complex example which is translated as follows: translate('select a+1 as c from test order by b,c') => table('test').project('a','b').addColumn('a+1', as='c').orderBy('b','c').project('a') would work fine as: table('test') // selected: [a, b, ...], hidden: [] .addColumn('a+1', as='c') // selected: [a, b, c, ...], hidden: [] .project('c') // selected: [c], hidden: [a, b, ...] .orderBy('b','c') // selected: [c], hidden: [a, b, ...] (not sure why there's a .project('a') on the end of their version) Which is a reasonably local, algebraic transformation.

samatman 2y ago

If you're interested in what it would take to put relational databases back on the rigorous footing of relational algebras, the Third Manifesto is a good place to start. https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf I find it somewhat sad that an implementation of a database with a proper D language hasn't broken out and become a ubiquitous tool for the profession. There were some proprietary versions shortly after the manifesto's publication, but it never caught on.

aoeusnth1 2y ago

I find that most people who object to SQL do not use TVFs. If you don’t have any tools to easily break down the steps of the work, of course SQL will feel like an opaque Write-only language. With TVFs you can easily iteratively add more complex steps to your query while checking your work while you build.

lbourdages 2y ago
What does TVF mean? I have been able to find anything on Google, all I get is an Indian streaming service... "Truth value function"?
- housecarpenter 2y ago
  Table-valued function.

r00fus 2y ago

That diagram separating the syntactic vs. semantic layers of a SQL statement (from Neumann & Leis paper) is brilliant.

chubot 2y ago

An analogy I like is - Are Perl-style regexes (used in Python, Ruby, Java, .NET, etc.) syntactic sugar for regular languages? The answer is no, because Perl added all sorts of imperative doodads to regexes, which can’t be easily represented and executed in the automata-based paradigm. Trying to do this is like a “research paper generator” (and not in a bad way), e.g. Derivative Based Nonbacktracking Real-World Regex Matching with Backtracking Semantics - https://dl.acm.org/doi/abs/10.1145/3591262 https://dl.acm.org/doi/abs/10.1145/3591262 (2023) This is until Go and Rust, which used automata-based regexes from the beginning. I don’t think users have lost much. Purely automata-based engines are kind of pleasant to write, because almost everything is in the compiler, and not in the runtime, e.g. https://github.com/andychu/rsc-regexp/blob/master/py/README.md https://github.com/andychu/rsc-regexp/blob/master/py/README.... That is, features like ? + * really are syntactic sugar for repetition. There’s also a lot of syntax sugar around character classes like [^a], and the runtime is very small. --- Likewise, SQL seems to have so many non-relational doodads in its language design, which cause problems for implementers. In this case, I think there’s an incentive problem with SQL: It benefits vendors if their dialect is harder to re-implement. Although certainly they’ve added many useful features too in 4-5 decades! To me a language design issue is we never really “learned” to compose languages with different paradigms: - the set-based paradigms like relational algebra and regular languages, with - Turing-machine like code. (and also I/O!) We never learned polyglot programming, so each language becomes its own source of “reckless growth” – its own parochial backwater. Both regexes and SQL should be able to “escape” to normal code, and that would greatly simplify them. This can be done both by language implementers and by application programmers, i.e. “factoring” across languages. It’s not always obvious how to do this, but it certainly it can be done more than we do it today. --- I’d argue the same phenomenon – lack of language composition – leads to programming languages within YAML. Github Actions is nominally some kind of “declarative” scheduler specification, or graph (job -> job dependencies), but that’s not enough for many problems. So it also has a bunch of doodads for escaping that model (to the extent it has a model). Shell, Awk, and Make also grew many doodads (https://www.oilshell.org/blog/2016/11/14.html https://www.oilshell.org/blog/2016/11/14.html), which are not very well designed. They used to be declarative languages, but no longer are. Although there is some distinction between “formerly set-based languages” like SQL and regex, and other “declarative” non-Turing-complete languages. But I think the language composition problem is approximately the same. Part of it is syntax, but a lot of it is semantics. (copy of lobste.rs comment)