Self Join

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
 A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

Examples:

USE ProductManagement

Go

SELECT DISTINCT pv1.ProductID, pv1.CategoryID

FROM dbo.Products pv1

INNER JOIN dbo.Products pv2

ON pv1.ProductID = pv2.ProductID

AND pv1.CategoryID = pv2.CategoryID

ORDER BY pv1.ProductID

Post By : Urvish Patel

Blog By : Dipen Shah

Stay tuned for more...