Most of us spend a decent part of our days writing relational database queries in a language that was designed in the 1970s. While the actual clauses of the query are very expressive, I could think of a lot of ways to clean it up.<p>One of my biggest pain points is that a CTE can't be used for more than one select statement. Basically a temporary view. Why can't I do:<p><pre><code> var @users = (
select *
from dbo.Users
where isActive = 1
);
select *
from @users;
select *
from Projects p
where exists (
select *
from @users
where userId = p.userId
);
</code></pre>
Or hell, even:<p>create view #Users as select * from dbo.Users where isActive = 1;<p>The compilation would be a trivial substition of the initial variable as subqueries:<p>select *
from (
select *
from dbo.Users
where isActive = 1
) x;<p>select *
from Projects p
where exists (
select *
from (
select *
from dbo.Users
where isActive = 1
) x
where userId = p.userId
);<p>I know that there are some very good ORMs that can sometimes be a solution. But sometimes you need SQL.<p>Why hasn't anyone developed a higher level language that compiles down? We've done it with LESS, SASS, CoffeeScript, haml.