The issue is that composability is often tied to actually moving data around in the database which has terrible performance. That is, you can compose a query of multiple queries that dump partial data sets into temp tables.
Views get you part of the way there, but they are designed to be long lived and are visible to all database users until they are dropped. This means its dangerous to change them or clean them up, as its not always clear who they are being used by.
Ephermal or temporary views that are session/connection based, or even loadable as modules would be useful to me.
Most databases offer the WITH syntax, but, bizarrely, the SQL standard specifies that the WITH block should materialised separately, which prevents it from being used as a device for abstraction.
Oracle is the only database I'm aware of that allows the WITH block, but doesn't impose the optimiser fence.
You mean like temp tables in MSSQL? They come in various flavours, they're all pretty handy. The most flexible of these is the XML variable, but there are far simpler options than that.
In contrast to most languages, I do find SQL is generally easier to understand when reading it, rather than writing it.
I would prefer a syntax layer that can be compiled / transformed back to SQL but that does basic things like having a query start with the tables, then joins, then groupings then the final projection.
Also a less cumbersome way to use the "WITH" statement to form named sub-queries.
Perhaps something like:
Being re-written as: So the final query may look like: Any thoughts on this?