It's OK for nested subqueries to use the same aliases as used in the parent query, although it might be a bit confusing for someone reading the code. The name space for aliases on a nested subquery is separate from the name space on the parent. For example the query below has a nested subquery b
that also has an alias b
used within it. This would be potentially confusing to the programmer but fine with the DBMS engine:
select a.foo
,b.bar
,b.BarCount
from (select b.bar
,count (*) as BarCount
from BarTable b
join OtherTable o
on b.OtherTableID = o.OtherTableID
group by b.bar) b
join Foobar a
on a.bar = b.bar
On a correlated subquery you have access to the parent's aliases, so the aliases must be unique across the parent query and correlated subquery. If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:
select a.foo
,b.bar
from Foobar a
join Bar b
on b.FooBarID = a.FooBarID
where not exists
(select 1
from Bar b2
where b2.BarCategoryID = b.BarCategoryID
and b2.BarDate > b.BarDate)
The correlated subquery does not have an alias as it does not participate in a join as such1. The references b
and b2
for bar
are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.
1 Note that the optimiser may choose to use join operators within the plan behind the scenes, although the actual operation specified is a correlated subquery and not a join against a nested subquery.