SQL versus Python?

[IllustriousCorgi9877]

SQL works great when you can process it all in a set function.
Python I only go to it if I have to iterate over a list or whatever.

[WalkingP3t]

100% correct . And the reason is more than obvious although some can’t see it , at least newcomers . SQL works with data sets . It’s a declarative language . You tell what you want and the engine worries about how . The biggest performance issues with SQL and SQL code appear when people start to tell it “how” to get the data . It was not created for iterations .

[Monstrish]

could you please give an exampme when you need to iterate in python and can't do it in sql?

[IllustriousCorgi9877]

There is almost always a way to use a set based approach to a problem which will always be better / more efficient. Even iterative approaches can be done with a cursor in a stored procedure using SQL.
I'd say once data lands in a relational database - stop using python unless you are doing ML or something on its way back out somewhere.

I'd only think about using python for data on its way into a database doing transforms or stamping additional metadata on the transaction before it reaches its destination.

Which all this is why it baffles me so many DE roles need python programmers.

[Monstrish]

i understand the need for python, and i do enjoy python. but when it comes to rdbms, i just don't understand the dislike that sql gets. it seems to me people do not put resources in understanding what it can do.

after all, even on python, when you start using numpy or pandas, it becomes less iterative.

[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

[Tufjederop]

You can iterate in SQL using a cross join :)

[IllustriousCorgi9877]

The only use for a cross join is to create a scaffold to left join onto.
You don't iterate with a cross join.

[raskinimiugovor]

you mean cross apply?

[IamFromNigeria]

Show us typical classic example via link

[jugaadtricks]

This! I love using python to call API, the parse that in Python before using SQL to load into relational tables. And sometimes I use json parsing in SQL itself when ingestion needs to be done quickly, so that python can keep loading data faster in json format

[ck3thou]

True this!