A self join is simply when you join a table with itself. There is no
SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.
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.
It is useful when you want to correlate pairs of rows from the same table, for example a parent – child relationship.
Some practical uses of SELF JOIN are to obtain running counts and totals, identify which data under a particular column satisfy a certain set of conditions, and generate another table by extracting data from the original table.
select * FROM Table t1, Table t2 WHERE t1.Id = t2.ID
Example: Using SELF JOIN to find the products supplied by more than one vendor.
Because this query involves a join of the ProductVendor table with itself, two different aliases (pv1 and pv2) are used in the FROM clause. These aliases are used to qualify the column names in the rest of the query.
Syntax:
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
select * FROM Table t1, Table t2 WHERE t1.Id = t2.ID
Example: Using SELF JOIN to find the products supplied by more than one vendor.
Because this query involves a join of the ProductVendor table with itself, two different aliases (pv1 and pv2) are used in the FROM clause. These aliases are used to qualify the column names in the rest of the query.
Syntax:
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