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 employees who have the same manager as Terri Duffy by using a subquery:

USE AdventureWorks;
GO
SELECT EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID IN
    (SELECT ManagerID
     FROM HumanResources.Employee
     WHERE EmployeeID = 12)

Here is the result set.

EmployeeID  ManagerID
----------- -----------
6           109
12          109
21          109
42          109
140         109
148         109
273         109

(7 row(s) affected)

Or you can use a self-join:

USE AdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
INNER JOIN HumanResources.Employee AS e2
ON e1.ManagerID = e2.ManagerID
AND e2.EmployeeID = 12

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 AdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
WHERE e1.ManagerID IN
    (SELECT e2.ManagerID
     FROM HumanResources.Employee AS e2
     WHERE e2.EmployeeID = 12)

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

See Also

Concepts

Subquery Types