If you compose SQL with subqueries you tend to do less composition in general (because you are restricted in what you can compose), and you are more at the mercy of the query planner, which is its own black box.
I'd rather simply understand how my ORM generates queries, and then use the ORM to get the full power of my language of choice in creating the right SQL queries.
> I'd rather simply understand how my ORM generates queries,
Depending on the ORM this may be be far from trivial. Despite being a C# dev rather than a DBA I can say that I have a better understanding at least one DBMS's query planner than what Entity Framework will generate for certain queries [0].
I'll note that -good- MicroOrms can make 'subqueries' in a fairly composable way.
I think with a manual SQL, you can still make a 'good' mini-DSL with subqueries, but it will take some thought to do well. Actually, with -extremely- thoughtful design it can be far more performant and productive, but IMO the complexity would need to show ROI to be worth it.
But in -either- case, in the long run the restrictions help with perf and maintenance. Whether it's hand-written mini-DSL or a good MicroORM DSL, you have a much better mechanical sympathy for what you're doing to the DB, and it becomes easier to write Table designs that are performant.
> and then use the ORM to get the full power of my language of choice in creating the right SQL queries.
Now you have to learn an ORM, a Query planner, -and- how to make them play nice.
[0] - There has been more than one shop where I was the 'EF Expert'... take that as you will.
I'd rather simply understand how my ORM generates queries, and then use the ORM to get the full power of my language of choice in creating the right SQL queries.