> You can only go so far with SQL as far as readability goesThis. Exactly this is the problem. SQL as a language fails to provide you with simple means of composition.
And this is where libraries like Python's SQLAlchemy come into play. Note that it is not necessary to use SQLAlchemy as an ORM. Even if you use it "just" as a query builder, it will simplify a lot.
Composition is really where the syntax of SQL fails utterly. This is quite surprising, because the foundation of SQL, relational algebra, excels at composability: You have many operations which all operate on the same type of data (namely, sets of tuples, or in math/cs speak: "relations"). As such, you can combine them in any way you want. For example:
* "Filter" takes a relation and returns a relation, just with fewer rows.
* "Projection" takes a relations and returns a relation, just with fewer columns
* "Cross Join" takes two relations and returns one relation.
* "Inner Join" is really just a combination of "Cross Join" and "Filter"
* ... and so on.
SQL's attempt to be more "human readable" than those nested operations fails to preserve that. Yes, we have sub selects, but can't just stick together the operations we want.
For example, assume you have an existing query (perhaps more complex than this one):
select a, b from t where a > 0
and want to apply a simple filter "b > 0" on top of it:
(select a, b from t where a > 0) where b > 0
This kind of composition is not allowed. You either have to either give up composition (no reuse of the existing query) and combine the filters by hand:
select a, b from t where a > 0 and b > 0
Or, you have to write a larger sub select:
select * from (select a, b from t where a > 0) as temp where b > 0
In relational algebra, the first statement would have been:
Projection[a,b](Filter[a>0](t))
Or, using ">" for nested function calls (function composition):
t > Filter[a>0] > Projection[a,b]
For the task at hand, you just compose it with your additional filter and be done with it, reusing 100% of your existing query:
t > Filter[a>0] > Projection[a,b] > Filter[b>0]
However, SQL forces you to either rewrite this query:
t > Filter[a>0 and b>0] > Projections[a,b]
Or to apply a sub select, which means adding nonsense operations such as naming a purely temporary intermediate result and projecting to all columns:
t > Filter[a>0] > Projection[a,b] > Name[temp] > Filter[b>0] > Projection[*]
In my view, the task of SQL query builders (such as the one in SQLAlchemy) is to restore the ability for programmers to form their query in relational algebra, without having to worry about the quirks added by the SQL language.