Search code examples
sqlsql-serveransi-sql

Is joining to the result of a select (rather than an actual table) valid ANSI standard SQL?

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?

Solution

  • 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.