Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Self-join
- A self-join is joining a table to itself.[6]
- [edit]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.[7]
- Consider a modified 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
- For this example:
- F and S are aliases for the first and second copies of the employee table.
- The condition F.Country = S.Country excludes pairings between employees in different countries. The example question only wanted pairs of employees in the same country.
- The condition F.EmployeeID < S.EmployeeID excludes pairings where the EmployeeID of the first employee is greater than or equal to the EmployeeID of the second employee. In other words, the effect of this condition is to exclude duplicate pairings and self-pairings. Without it, the following less useful table would be generated (the table below displays only the "Germany" portion of the result):
- EmployeeID LastName EmployeeID LastName Country
- 305 Smith 305 Smith Germany
- 305 Smith 306 John Germany
- 306 John 305 Smith Germany
- 306 John 306 John Germany
- Only one of the two middle pairings is needed to satisfy the original question, and the topmost and bottommost are of no interest at all in this example.
Add Comment
Please, Sign In to add comment