A table can be joined to itself in a self-join. For example, you can use a self-join to find the products that are supplied by more than one vendor.
Because this query involves a join of the
ProductVendor table with itself, the
ProductVendor table appears in two roles. To distinguish these roles, you must give the
ProductVendor table two different aliases (
pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:
USE AdventureWorks; GO SELECT DISTINCT pv1.ProductID, pv1.VendorID FROM Purchasing.ProductVendor pv1 INNER JOIN Purchasing.ProductVendor pv2 ON pv1.ProductID = pv2.ProductID AND pv1.VendorID <> pv2.VendorID ORDER BY pv1.ProductID