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?
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.
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.
Short answer:
Cause its the easiest way to work with tables from subquery
Long answer:
But we can fantasize and imagine how it would be possible to optimize the syntax of SQL queries. I also think about this. Dont know exactly, but it seems like...
if you have query, that return subquery with columns that different from what you have in higher level, seems there no need to have an alias
select id, column1 from t1 left join ( ...some query... ) t2_alias using(id) seems that t2_alias no need
But! Expect, you have to define not equals operation (not operator using), but something like this:
select id, column1 from t1 left join ( ...some query... ) t2_alias on t1.id > t2_alias.id In this case you need to define each id with its table (to know what table id must be bigger than another)
So, if we take Postgres, for example, they could do an optimisation: not to require alias if join have equals operator. But it seems like too excessive to make such an optimization for just 1 operator. What about other 1000... cases?
So, its easy to leave contract that alias is always required in subquery.
FROMclause have an alias.