Correlated Subqueries with Aliases

Correlated subqueries can be used in operations such as selecting data from a table referenced in the outer query. In this case a table alias (also called a correlation name) must be used to specify unambiguously which table reference to use. For example, you can use a correlated subquery to find the products that are supplied by more than one vendor. Aliases are required to distinguish the two different roles in which the ProductVendor table appears.

USE AdventureWorks2008R2;
GO
SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
FROM Purchasing.ProductVendor pv1
WHERE ProductID IN
    (SELECT pv2.ProductID
     FROM Purchasing.ProductVendor pv2
     WHERE pv1.BusinessEntityID <> pv2.BusinessEntityID)
ORDER  BY pv1.BusinessEntityID

The preceding nested query is equivalent to this self-join:

USE AdventureWorks2008R2;
GO
SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
    AND pv1.BusinessEntityID <> pv2.BusinessEntityID
ORDER BY pv1.BusinessEntityID