Microsoft SQL Server allows this (using the Pubs database).

select 
    titles.title_id, title, TotalSales 
from 
    titles 
join 
    (select title_id, sum(qty) as TotalSales
     from sales 
     group by title_id) as ts on ts.title_id = titles.title_id

where the join is to the result of a select rather than an named table or view.

Is this valid ANSI standard syntax?

If it's not standard are there other well known databases that support it?

CodePudding user response：

Yes, this is standard ANSI SQL, it's known as a derived table and is a building block of most SQL dialects.

In SQL it's perfectly valid to treat the result af a query as a table in its own right and reference it (with an appropriate alias) in any "parent" query.

It's supported by almost all RDBMS platforms.

I say almost as a caveat but I can't actually think of any currently supported platform that would not allow a derived table.