Subqueries with Aliases

Many statements in which the subquery and the outer query refer to the same table can be stated as self-joins (joining a table to itself). For example, you can find addresses of employees from a particular state using a subquery:

USE AdventureWorks2008R2;
GO
SELECT StateProvinceID, AddressID
FROM Person.Address
WHERE AddressID IN
    (SELECT AddressID
     FROM Person.Address
     WHERE StateProvinceID = 39)

Here is the result set.

StateProvinceID AddressID

----------- -----------

39 942

39 955

39 972

39 22660

(4 row(s) affected)

Or you can use a self-join:

USE AdventureWorks2008R2;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
INNER JOIN Person.Address AS e2
ON e1.AddressID = e2.AddressID
AND e2.StateProvinceID = 39;

Table aliases are required because the table being joined to itself appears in two different roles. Aliases can also be used in nested queries that refer to the same table in an inner and outer query.

USE AdventureWorks2008R2;
GO
SELECT e1.StateProvinceID, e1.AddressID
FROM Person.Address AS e1
WHERE e1.AddressID IN
    (SELECT e2.AddressID
     FROM Person.Address AS e2
     WHERE e2.StateProvinceID = 39)

Explicit aliases make it clear that a reference to Person.Address in the subquery does not mean the same thing as the reference in the outer query.