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.
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:
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
);
Or hell, even:
create view #Users as select * from dbo.Users where isActive = 1;
The compilation would be a trivial substition of the initial variable as subqueries:
select *
from (
select *
from dbo.Users
where isActive = 1
) x;
select *
from Projects p
where exists (
select *
from (
select *
from dbo.Users
where isActive = 1
) x
where userId = p.userId
);
I know that there are some very good ORMs that can sometimes be a solution. But sometimes you need SQL.
Why hasn't anyone developed a higher level language that compiles down? We've done it with LESS, SASS, CoffeeScript, haml.
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:
Or hell, even:
create view #Users as select * from dbo.Users where isActive = 1;
The compilation would be a trivial substition of the initial variable as subqueries:
select * from ( select * from dbo.Users where isActive = 1 ) x;
select * from Projects p where exists ( select * from ( select * from dbo.Users where isActive = 1 ) x where userId = p.userId );
I know that there are some very good ORMs that can sometimes be a solution. But sometimes you need SQL.
Why hasn't anyone developed a higher level language that compiles down? We've done it with LESS, SASS, CoffeeScript, haml.