I use the same alias (RankedPrice) twice in my query. I expected that to cause an error. But it worked well. Why does this happen ? I was expecting it throw a variable scope error like C++ or Java, but it does not happen.
select *
from
(select *, RANK() over(order by retailprice) as RankedPrice
from CurrentProducts) as RankedPrice
where RankedPrice = 5
Because of the way that SQL works, you can never have any confusion about what type of object you're referencing, in any particular reference.
If you have SELECT a, ..., you know that a is a reference to a column. It can't be a reference to a table, a database, a stored procedure, a UDF, etc.(*)
If you have SELECT a.b, ..., you know that a is a reference to a table(+), and that b is a reference to a column.(*)
So, there's no logical reason to preclude a table from containing a column where both the table and the column have the same name.
(*) - this applies in the SELECT clause. In the FROM clause, a single-part name can only be a table(+), a two-part name can only be schema + table, etc. But in each clause, the type of objects being referenced is always unambiguous.
(+) - well, what I really mean here is a row source - a table, a view, a CTE. Something that, if you wanted to, you could construct as a table and use in its place in the query.
As others have said, there's also no obstacle to introducing the same alias in a subquery. But that's not happening here, because we end up with an object that can (properly) be referenced as RankedPrice.RankedPrice.
Parent and nested subqueries have different namespaces for aliases. It is therefore okay for nested subqueries to use the same aliases, but it might be a bit confusing for someone reading the code.
That said in your code RankedPrice is used first as a column alias and then as a derived table alias, though the aforementioned namespace sepreration prevents any collisions.
I should also point out that correlated subqueries have a single shared namespace.
