You are thinking about the declarative part. SQL/STP, the standard that's the basis for definition of the imperative part of SQL (stored procedures) can compose in a similar way (eg. you can call a stored procedure from a stored procedure), but the standard doesn't go far enough, and so every practical database has its own extensions which deters people from using stored procedures because they'd end up with non-portable code.
stored procedures, being procedural, are the antithesis of a declarative database library.
I'm talking about this:
Query 1: SELECT a, b, c FROM table1 JOIN table2 ...
Query 2: SELECT d, e, f FROM (SUBSELECT ... ) AS table3 JOIN table4 ...
Now, join those queries together. How? Well you have to analyze the join clauses in from and then combine those together, then rewrite the SELECTION. Okay, great... not so hard. Now use Query 2 in a recursive CTE for Query 1. Oh goodness... much harder. Or what about:
Query 1: SELECT a, b, c FROM table1 JOIN table2 ...
Query 2: SELECT d, e, f FROM (SUBSELECT ... ) AS table3 JOIN table4 ... ORDER BY table4.column LIMIT 10
Now join 1 and 2 together... the syntax is much much much different.
Some SQL libraries in Haskell, like Beam, Opaleye, Squeal, etc, demonstrate the kind of composability I mean. In particular, all three of these offer a monadic interface where arbitrary queries can be joined using well-known relational operators (relational cross joins form a monad). I'm not sure of Opaleye and Squeal, but Beam does it's best to produce a human-comprehensible query.
Moreover, the first queries given above are 'easy' in the sense that the portion after the `FROM` in a regular SQL SELECT does actually compose well, since the JOIN operators are basically a direct translation of the underlying relational algebra. However, they start to fail when you want to join up aggregations and the like. While true that aggregations and ordering are not strictly relational, it is of great use to be able to compute with such things.
Notice how we are able (in the second to last query), simply combine the query `all_ (track chinookDb)` with the complex aggregate expression. In this DSL, the entire aggregate could be floated out. In particular, notice how the queries for either the first `all_` or the second complex aggregate would look much different on their own. That's what I mean by SQL doesn't compose.
Of course all these DSLs have the problem that they're trying to work with an existing language SQL that is not nice and orthogonal, and so they all have their varying deficiencies.
Datalog mostly fixes these issues and provides a nice model.
In datalog you can. Either by using the comma or semicolon operator.