Subqueries' aliases same as main queries' aliases

Question

I have an SQL query whose aliases are the same as some of its subquery's aliases.

For example:

select *
from ROOM r
where ...
         (
              select *
              from ROAD r
              where ...
         )

This works fine, as the subquery's alias seems to hide the main one's.

1. Will it work that way in all cases?
2. Will I ever get undefined results?
3. If it's OK to do that, how can I make a reference to the main query's r?

Answer 1

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 such¹. The references b and b2 for bar are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.

---

¹ 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.

Answer 2

ConcernedOfTunbridgeWells, you write (emphasis mine): "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."

I don't believe uniqueness is required. I believe that, if an alias is used in a correlated subquery as a correlation name, as well as a table alias in the outer query, the alias in the subquery will take precedence.

Example:

CREATE TABLE #T (A INT)
CREATE TABLE #U (A INT)
CREATE TABLE #V (A INT)

INSERT INTO #T (A) VALUES (1), (2), (3)
INSERT INTO #U (A) VALUES (2), (3), (4)
INSERT INTO #V (A) VALUES (3), (4), (5)

SELECT
    T1.A
FROM
    #T AS T1
    INNER JOIN #U AS T2 ON T1.A = T2.A
WHERE
    EXISTS (SELECT * FROM #V AS T2 WHERE T1.A = T2.A)

The output is "3": tables T and U have 2 and 3 in common, but the WHERE predicate further filters the rows returned to 3, and 2 does not exist in V.