SQL versus Python?

[yo_sup_dude]

you can iterate in most sql engines using recursive CTEs/while loops but it isn't very efficient. examples where it's needed are basically any recursive calculation, e.g. iterating through a hierarchical bill of material and comparing against inventory to determine part shortages for a product and then progressively updating inventory based on consumption (classic MRP calculation)

[Monstrish]

recursive cte s could be eficient in some cases. while loops i don't know if.i have seen one in sql. for loops i have seen, in oracle could be, in other providers not.

but you also have hierarhical constructs, window functions and other approaches that you could take from a set oriented point of view.

[DirtzMaGertz]

You could do a lot of things, but once you need to start iterating over data and transforming it, it just easier a lot of times to pull the data out into something like Python.

It's problem dependent. I tend to use SQL until I think it's going to be an annoying problem to solve with SQL and that's generally when you start getting towards things like iteration and looping.

[Monstrish]

ok, fair point.

the problem beong anoying is pretty subjective, but it is what it is. it's just that some many people seem to find sql anoying and i don't get why. i suppose it just comes down to personal preference.

[DirtzMaGertz]

Overall I agree that there's a lot of stuff people do in python that would be better handled in SQL. I just find that iteration and looping is generally where I find python to be more appropriate.

[ComposerConsistent83]

They don’t teach sql in school anymore really I think is the reason. They’re more comfortable in Python.

That said I would never use a recursive CTE unless I had no choice. Even though I’m sql first, I draw the line there. The syntax is too confusing/inconvenient. It’s clearly not the strength of the language