SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows.
And there are often problems when using multiple SELECT queries in a batched statement: you can't re-use existing CTE queries. Not all client libraries support multiple result-sets. It's essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn't even support named result sets...), which means you can't opportunistically skip or omit a SELECT query in a batch because your client reader won't know how to parse/interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you'll run into concurrency issues if data changes between SELECT queries in the same batch ()
And there are often problems when using multiple SELECT queries in a batched statement: you can't re-use existing CTE queries. Not all client libraries support multiple result-sets. It's essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn't even support named result sets...), which means you can't opportunistically skip or omit a SELECT query in a batch because your client reader won't know how to parse/interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you'll run into concurrency issues if data changes between SELECT queries in the same batch ()
[1] https://learn.microsoft.com/en-us/ef/core/performance/effici... and https://learn.microsoft.com/en-us/ef/core/querying/single-sp...