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.
- Will it work that way in all cases?
- Will I ever get undefined results?
- If it's OK to do that, how can I make a reference to the main query's
r
?
Best Answer
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 aliasb
used within it. This would be potentially confusing to the programmer but fine with the DBMS engine: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:
The correlated subquery does not have an alias as it does not participate in a join as such1. The references
b
andb2
forbar
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.