SELF-JOIN

Ref: http://www.udel.edu/evelyn/SQL-Class3/SQL3_self.html

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.  One practical use for self-joins:  obtaining running counts and running totals in an SQL query.

To write the query, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Example

Which customers are located in the same state (column name is Region)?  Type this statement in the SQL window:

SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region

FROM Customers AS c1, Customers AS c2

WHERE c1.Region = c2.Region

AND c1.ContactName <> c2.ContactName

ORDER BY c1.Region, c1.ContactName;

The result should look like this:

Exercise

Which customers are located in the same city?  (32 rows)

 

Ref: http://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-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.

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