Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
1  
Short answers are "1.Yes", "2.No" and "3.in that case, you can't (so it's not really OK if you want to make such a reference)" –  ypercube May 21 '12 at 21:10
add comment

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
The subquery in the first query is a derived table and standard SQL requires it to always be given a name: there is no logical reason for this requirement but SQL Server has implemented it anyway, though in the particular example you have chosen a name is indeed required. The subquery in the second query is not a derived table, hence why it does not require a name (the fact it is a correlated subquery is immaterial). –  onedaywhen Apr 16 '12 at 8:02
    
@onedaywhen - I can't think of any situation but a correlated subquery where the subquery needs access to aliases used in the parent. Did you have something specific in mind? –  ConcernedOfTunbridgeWells Apr 16 '12 at 9:28
    
I'm not sure I understand your question. Perhaps I should make it clear that I was specifically responding to your comment, "The correlated subquery does not have an alias as it does not participate in a join as such." My response was supposed to convey the point that the rules regarding range variables (what the SQL standard calls 'correlation names' and you call 'aliases') are not directly related to their participation (or otherwise) in joins. –  onedaywhen Apr 16 '12 at 10:39
    
Simple example: SELECT * FROM ( SELECT c FROM T ) AS T2; -- no joins, no correlation yet the SQL standard requires that the derived table be assigned a range variable (T2 in this case). –  onedaywhen Apr 16 '12 at 10:44
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.