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.
When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.
Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.
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
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – Example courtesy
46 Comments. Leave new
Gud Arcticle.
liked it. Its a good article
Nice Article
very nice article
didnt understand please give an easier example i am just learning SQL please forward me 1 if possible all help will be appriciated
i want to know detail info about cursor and trigger can u help me .
With your example, I have the following problem:
List pairs of vendor names who have the same ProductID. But if I use Self Join like the below script, the result will be redundant (2 pairs are the same except their orders). What should I do to get the exact pairs I need? Thank you.
USE AdventureWorks;
GO
SELECT DISTINCT pv1.VendorName, pv2.VendorName
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID != pv2.VendorID
(The result will be:
Vendor1 Vendor2
Vendor2 Vendor1
Vendor3 Vendor4
Vendor4 Vendor3
…
)
Keep it Up
nice article on self join
Nice Example
Nice Example
very nice article for self join……………
Gud article for selef join..
I think that the example needs to be corrected in the AND part of the code. ProductID needs to be the same, but the VendorID must be different to get products supplied by multiple vendors!
AND pv1.VendorID pv2.VendorID
I think above example needs to be modified to get the correct result,
i have used cte(common table expression) for that:
with vendorcount(productidcount) as
(
select productid from purchasing.productvendor
group by productid having count(vendorid)>1
)
select productidcount,vendorid from vendorcount inner join Purchasing.ProductVendor
on productidcount=productid
any suggestions would be appreciated
You are one man with great words.
Excellent!!!
I think the query should be modified as below:
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
it works efficiently
AND pv1.VendorID pv2.VendorID
Administrator :
I am not able to place “not equal to ”
between pv1.VendorID And pv2.VendorID