alt Hacker NewsI’ve always felt that SQL is somewhat easy to grasp for basic queries, but gets complex and difficult for even moderate to higher complexity use cases. My eyes glaze over when I read long stored procedures that someone else has written. Any recommended resources to go from beginner/beginner-intermediate to advanced?
Replies
I feel like stored procedures and co crosses over into the realm of application programming, and while I can't speak from experience (so take this with a huuuuge grain of salt), this is where things break down. It feels like adding logic / basic programming to JSON/YAML, which are data/config languages primarily.
I think stored procedures - or anything that goes beyond storing / looking up data - had a place when a database had multiple different clients, but with modern day systems that's less likely to be an issue.
PRQL, or https://www.malloydata.dev/
The way to learn advanced SQL is to challenge yourself to find a set oriented solution and avoid procedural code. The more unreasonable it feels, the more you learn.
If the solution you find is longer and not much faster than the procedural alternative, you throw it away and fall back on procedural code.
Stored procedures are not advanced SQL. Most of them are not SQL at all. There are a few legitimate reasons for using SPs such as reducing roundtrips to the database and writing little pure functions for use in SQL statements.
But many uses of SPs are just laziness or a symptom of organisational dysfunction.