SQL Self Join - All employees in same country

self-join is joining a table to itself

Example

A query to find all pairings of two employees in the same country is desired. If there were two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, a normal join operation could be used to find the answer table. However, all the employee information is contained within a single large table.

Consider Employee table such as the following:

Employee Table
EmployeeID	LastName	Country	DepartmentID
123	Rafferty	Australia	31
124	Jones	Australia	33
145	Steinberg	Australia	33
201	Robinson	United States	34
305	Smith	Germany	34
306	John	Germany	NULL

An example solution query could be as follows:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country

FROM Employee F

INNER JOIN Employee S ON F.Country = S.Country

WHERE F.EmployeeID < S.EmployeeID

ORDER BY F.EmployeeID, S.EmployeeID;

Which results in the following table being generated.







Employee Table after Self-join by Country
EmployeeID LastName EmployeeID LastName Country
123 Rafferty 124 Jones Australia
123 Rafferty 145 Steinberg Australia
124 Jones 145 Steinberg Australia
305 Smith 306 John Germany

SQL Self Join – All employees in same country

Leave a Reply Cancel Reply