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

Question

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?

SQL Server requires that all subqueries in the `FROM` clause have an alias. — Gordon Linoff, Oct 02 '18 at 21:54
Thanks. that a peculiarity of T-SQL (and it looks like postgresql...) or universal for SQL? Is there a why, or maybe it's an excessively technical explanation? — bbb0777, Oct 02 '18 at 22:04
Does this answer your question? [subquery in FROM must have an alias](https://stackoverflow.com/questions/14767209/subquery-in-from-must-have-an-alias) — philipxy, Nov 01 '21 at 10:03

score 3 · Accepted Answer · answered Oct 02 '18 at 22:15

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.

score 2 · Answer 2 · answered Oct 02 '18 at 22:08

every subquery in the FROM need to have alias name in SQL

You have to define so you can add further constraints to your query. otherwise, your DB Engine willn't know how to refer to the subquery.

We can think of the subquery result as a new table, but this table has no name, so give him an alias.

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

2 Answers2

Linked