[AnonHP]

I’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?

[dust-jacket]

My advice is: don't write complicated SQL.

The best thing I learned about SQL is that it can do an awful lot of clever stuff but that the vast majority of the time you really don't need it. Learn the basics. Shrug the rest off.

[mrhottakes]

This is the correct way. Much like any other kind of code, if you find yourself doing something "clever" it's time to think about whether you're really going down the right path.

[sgarland]

What do you consider to be clever SQL?

[9rx]

You don't have to get very clever. Once you get beyond inner join it starts to stick out like a sore thumb that SQL isn't relational and that you should have chosen a relational engine instead. SQL shines when you have an ORM and need a reliable backing store, though. Tradeoffs, as always.

[sgarland]

The only true relational database engine I’m aware of is Rel [0], which is a learning tool at best, not a production-capable DBMS.

I understand that SQL is pseudo-relational, but it’s as good as you’re going to get if you want something you can use for actual workloads.

0: https://reldb.org

[9rx]

Postgres was relational for the first decade of its life, until the infamous '95 release when it sold its soul to the devil. You could dig up on old version, perhaps. But, yes, the tradeoff for real-world use was already mentioned.

The relational model is superior as complexity grows (although arguably the deductive model is even better), but the tablational model is superior for simple line-of-business stuff, so pick your poison. Like with all things engineering, there is never one perfect solution that satisfies everything. If, like the earlier comment suggested, you keep your SQL simple then you'll be fine.

[oulipo2]

PRQL, or https://www.malloydata.dev/

[thelastgallon]

Yaml?

[efromvt]

I think advanced SQL authoring is generally simple to understand, and that's the larger learning curve!

I find those big stored procedures usually fall into two categories; logic that should be in the DB, but should be decomposed (staging tables, other SPs, etc) in which case they can be understandable in chunks; or logic that shouldn't be in the DB but has been shoved in there, in which case there's more of an ideological debate but I generally prefer to pull out and run in the application layer. (the latter is pretty much IMO the things that you've done after you've gotten the data at the right grain, when you are massaging it to a particular form/presentation format; performance is often the final arbitre here though).

[fauigerzigerk]

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.

[Cthulhu_]

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.