I’m sorry I used the imprecise term “relational algebra” to refer to the semantic model of SQL. My point is that the expression tree of a SQL statement is not made of loops, it’s made of table expressions (with join operations), column references, aggregates, filters, CTEs, etc. To oversimplify again, it’s mostly about set operations, not loops.

Of course in the query planner it gets turned into something executable, which will sometimes involve loops, but that’s not the model of the language itself. Python expressions get turned into bytecode so they can be executed, but you don’t write Python as a way of expressing bytecode.

To be concrete, the semantic expression tree of

select users.id, (select sum(posts.likes) from posts where posts.user_id = users.id) from users

isn’t

R=[]
for (u in users) {
  x=[]; for (p in posts) { if (p.user_id = u.id) x+=p.likes }
  R+=(u.id, sum(x))
}
return R

it’s more like

users
  .join(posts
    .filter(user_id=users.id)
    .groupby()
    .project(sum(likes) as sum))
  .project(users.id, posts.sum)

(Which in 2024 would be a much more natural way to write it in the first place, which is why I mentioned LINQ.)

In my experience writing and helping others to write SQL, if you write it thinking that you’re writing loops, it’s not as effective as if you write it thinking about set operations, especially as the queries get more complex. Not to mention that the error messages will actually make sense! To me this is like writing Rust thinking about structs, traits, borrows, etc. rather than thinking about assembly (99% of the time).

I’m not a SQL expert (though I have dug into the PG query planner on occasion) so maybe I’ve been doing this wrong, but it does seem to work.

---

score 3 · canonical