Sure, writing SQL won't solve design problems, but some ORMs need multiple queries to deliver data with relationships, joins, etc, that could be done in a single SQL query.
Where it might get trickier is when you have something like a Customer-Region-Vendor join, and you actually want it to go ahead and create objects for the all of the Customers and Regions and Vendors whose data was pulled out of that one select.
It even offsets some of the burden to the app, which is generally easier to scale than the database.
The idea is that you query the "root" table, loop over the results and build an array of IDs, then do additional queries to the other tables with a "where whatever_id IN (...)".
Built in support for WHERE subqueries, on the other hand, is on our roadmap. Currently working to finish core MSSQL support first though. Hope that helps!