Search code examples
sqlsql-servert-sqlalias

(T-SQL) Why does this subquery need an alias?

SELECT 
*

FROM

 (SELECT 
    ROW_NUMBER() OVER (PARTITION BY a.vendorid ORDER BY a.CreatedDateUTC) as RowNum
    ,*

 FROM 
    ZpVendors_Kim.dbo.VendorPaymentAcceptanceAudit a) Needs_Alias_Here

 WHERE 
    RowNum = 1

Very simple query, just wondering - why is an alias needed for it to work?

Solution

  • The alias after the subquery (or derived table, if you prefer) is required by SQL Server. It is not only a requirement but a really good idea. In general, column references should be qualified, meaning that they include a table alias. Without an alias, references to columns in the subquery could not be qualified. I think that's a bad thing.

    SQL Server is not the only database that requires the alias. MySQL and Postgres (and hence most Postgres-derived databases) do as well. Oracle and SQLite do not. Nor does Google's BigQuery.

    I do not know if the alias is an ANSI/ISO requirement. However, I always use one, regardless of the database.