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 (pv1 and 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

 See Also

Concepts

Using Operators in Expressions
Joining Three or More Tables
Using Inner Joins
Using Outer Joins
Using Cross Joins

Other Resources

WHERE (Transact-SQL)
SELECT (Transact-SQL)
Operators (Transact-SQL)
SELECT Examples (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance