Any cursor-for-loop that runs a query can be rewritten into one single relational query. Actually, if you think about it, a cross-join is nothing but a for-loop…
Ever since that day, it became my “go to story” on how differently a programmer and a SQL developer think about data processing.
Relational Algebra — The Foundation of SQL
Have you ever wondered why the sequence of JOINs in your queries doesn’t really matter? Well, that’s because computers don’t really run the query the way you see it… they translate your query using “relational algebra”, and in relational algebra inner joins are commutative (just like a+b = b+a, A⋈B =B⋈A ).
Relational Thinking — Technique #1 — Inner Join
Databases do very well with joins though, and a SQL Developer should easily solve this problem with simple relational algebra:
“Relational Algebra” Thinking — Technique #2 — Windowed Function
Relational algebra is extremely powerful and can solve all sorts of complex problems, but it requires a very specific way of thinking: Relational Algebra Thinking!