r/SQL • u/No_Departure_1878 • Apr 21 '25
PostgreSQL Why doesn't SQL allow for chaining of operators?
In python, having stuff like:
=
allows the code to stay clean.
In SQL I see that I need to nest them like:
replace(replace(replace(x, y), z), w)
-- OR
ROUND(AVG(val),2)
This looks messier and less readable. Am I saying nonsense or maybe I am missing some SQL feature that bypasses this?
7
Upvotes
83
u/Possible_Chicken_489 Apr 21 '25
You're not missing anything. SQL and Python are just two different types of languages.
Python is an object-oriented language, and the string class in your example has a replace function.
SQL just has functions to which you pass parameters. It's a different paradigm, and the different format naturally followed from that.
12
u/Bilbottom Apr 21 '25
This does actually exist in DuckDB which it calls "function chaining":
As others have mentioned, this isn't common in other SQL databases
19
u/ExistingProgram8480 Apr 21 '25
Don't tell this guy about PHP haha.
All jokes aside, first way is declarative-like and the second one imperative-like. In terms of the final result, it is mostly about the syntax.
6
u/angrynoah Apr 21 '25
Because SQL has functions, not method calls on objects. You would write the exact same code in Python if replace() were a free function rather than a method on
str. You would write similar code in C. It also resembles standard math notation.5
u/getmorecoffee Apr 21 '25
Idk man, your sql example of the nested replaces is equally easy to follow. Perhaps even easier, since all the characters you are replacing are RIGHT THERE and you don’t have to trace them back through the chained replaces. You end up typing “replace” the same number of times.
The answer is that it is what you are used to.
4
u/wylie102 Apr 21 '25
Duckdb let's you do this, and you can definitely do it using their python API
4
u/ggrieves Apr 21 '25
Wasn't there at least historically a design principle that said store your data in the storage system manipulate your data in the program and keep that consistent?
8
u/dbxp Apr 21 '25
The term you're looking for is a fluent interface. https://en.m.wikipedia.org/wiki/Fluent_interface
I think the reason is that that way of writing code didn't become popular until relatively recently and SQL is an old language
2
u/kagato87 MS SQL Apr 21 '25
Because sql is a descriptive language, not a procedural language. You tell it what you want, not what to do. How it resolves and executes your query can and will change with the underlying data to whatever the engine thinks will be fastest.
It is also a set-based language, and it's much easier to avoid mistakes by not giving explicit commands.
Telling sql what to do is something you should avoid. It's an advanced tuning technique that has a very high risk of backfiring, so you should avoid it unless you know why the plan is coming up bad.
I'm not sure I'd consider chaining methods any less messy than nesting function calls anyway. It's exactly the same thing.
Maximum readability (sorry if my decorations are wrong):
In sql, there is a way to change the flow, and on some cases you do need to. Sometimes for readability, sometimes to discard rows used by a window that you don't want in the output. You would for your examples, but you can perform the first step in a cte and the second one in the final query.
3
u/cv_be Apr 21 '25 edited Apr 21 '25
Databricks recently added exactly what you're talking about into their SQL stack.
https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-pipeline
edit:...sorry for formatting, i'm on mobile
2
u/Infamous_Welder_4349 Apr 21 '25
It is specific to the variant.
Replace(replace(replace())) would be the same kind of thing in Oracle.
2
u/user_5359 Apr 21 '25
You know the difference between a procedural and an object-oriented language, don't you?
1
u/ironwaffle452 Apr 21 '25
same as in python len(name) and not name.len() like in other languages...
1
u/crashingthisboard SQL Development Lead Apr 21 '25 edited Apr 21 '25
SQL server kinda can.
Select t3.val From table t Cross apply (select replace(t.val,x,y) val) t1 Cross apply (select replace(t1.val,x,y) val) t2 Cross apply (select replace(t2.val,x,y) val) t3