Editing Join (SQL) (section)

===Example===
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.<ref>Adapted from {{harvnb|Pratt|2005|pp=115–6}}</ref>

Consider a modified <code>Employee</code> table such as the following:
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+Employee Table
|-
! EmployeeID !! LastName !! Country !! DepartmentID
|-
| 123 || Rafferty || Australia || 31
|-
| 124 || Jones || Australia || 33
|-
| 145 || Heisenberg || Australia || 33
|-
| 201 || Robinson || United States || 34
|-
| 305 || Smith || Germany || 34
|-
| 306 || Williams || Germany || {{null result}}
|}
{{clear}}

An example solution query could be as follows:

<syntaxhighlight lang=sql>
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;
</syntaxhighlight>

Which results in the following table being generated.
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+Employee Table after Self-join by Country
|-
! EmployeeID !! LastName !! EmployeeID !! LastName !! Country
|-
| 123 || Rafferty || 124 || Jones || Australia
|-
| 123 || Rafferty || 145 || Heisenberg || Australia
|-
| 124 || Jones || 145 || Heisenberg || Australia
|-
| 305 || Smith || 306 || Williams || Germany
|}
{{clear}}
For this example:
* <code>F</code> and <code>S</code> are [[alias (SQL)|aliases]] for the first and second copies of the employee table.
* The condition <code>F.Country = S.Country</code> excludes pairings between employees in different countries. The example question only wanted pairs of employees in the same country.
* The condition <code>F.EmployeeID < S.EmployeeID</code> excludes pairings where the <code>EmployeeID</code> of the first employee is greater than or equal to the <code>EmployeeID</code> 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):
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
! EmployeeID !! LastName !! EmployeeID !! LastName !! Country
|-
| 305 || Smith || 305 || Smith || Germany
|-
| 305 || Smith || 306 || Williams || Germany
|-
| 306 || Williams || 305 || Smith || Germany
|-
| 306 || Williams || 306 || Williams || Germany
|}
{{clear}}
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.