I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL.
You can always use Google to look up the SQL, or now ChatGPT to generate it for you.
SQL is a not-that-great language and it intentionally hides what's going on.
It is also different enough between databases that you need to pay attention.
So learning to design/think in terms of SQL is probably not worth doing.
The set of data structures that you use to model and index a dataset is worth understanding, and designing in that space is a skill worth learning.
You’re kinda right, but designing for a particular RDBMS with awareness of queries which will be performed thus indexes necessary (…or not ;) is really not that far away from what you propose. The only issue is beginner SQL learning material says ‘it’s declarative, don’t worry about what’s happening as long as you get a good result’ and that just isn’t true in any non-trivial applications of SQL.
This is rather old and there are better ways of doing most of these things now. For instance the counting example would usually be much more efficient performed using the ROW_NUMBER() window function instead of a Cartesian product. When you can remove a cross product from your process it is almost always beneficial to do so. That will often involve introducing a CTE⁰¹ which might put off some beginners²³, but it shouldn't as this sort of example is pretty simple (you aren't worrying about any recursive case).
----
[0] Because you want the ordinal of the row in the input table/view, not your output.
[1] You could also use a sub-query, in most cases a good query planner will see the equivalence and do the same thing for either. The CTE option is easier to read and maintain IMO.
[2] In databases, like sports, CTEs can result in headaches!
[3] Or veterans of postgres, where until a few years ago CTEs were an optimisation gate, blocking predicate push-down and making some filtered queries a lot more expensive (though often no more so than the naive Cartesian product method).
8 comments
[ 4.5 ms ] story [ 36.6 ms ] threadThe set of data structures that you use to model and index a dataset is worth understanding, and designing in that space is a skill worth learning.
----
[0] Because you want the ordinal of the row in the input table/view, not your output.
[1] You could also use a sub-query, in most cases a good query planner will see the equivalence and do the same thing for either. The CTE option is easier to read and maintain IMO.
[2] In databases, like sports, CTEs can result in headaches!
[3] Or veterans of postgres, where until a few years ago CTEs were an optimisation gate, blocking predicate push-down and making some filtered queries a lot more expensive (though often no more so than the naive Cartesian product method).
And with SQL macros becoming a thing it is now easily possible to store patterns as reausable functions with parameters.