4.4. Self Joins

On some occasions, you will need to join a table with itself. Joining a table with itself is known as a self join.

In a regular join, a row of a table (Table A) is joined with a row of another table (Table B) if the column value used for the join in Table A matches the column value used for the join in Table B. One row of a table is processed at a time. But, if the information that you need is contained in several different rows of the same table, for example if you need to compare row1, column1, with row2, column1, you will need to join the table with itself.

Suppose that we want to find all the students who are more senior than other students. We have to join the Student table with itself. Logically, we need to take a row from the Student table and look through the rest of the Student table to see which rows fit the criterion ("more senior"). To accomplish this, we will use two versions of the Student table. Here is our query:

SELECT 'SENIORITY' = x.sname + ' is in a higher class than ' + y.sname
FROM Student AS x, Student AS y
WHERE y.class = 3
AND x.class > y.class

First we alias the Student table as x, and then we alias another instance of the Student table as y. Then we join where x.class is greater than y.class and we added the WHERE qualifier y.class = 3, so this effectively gives us only the seniors. We restricted the result to "just seniors" to keep the result set smaller). The use of the > sign is also an example of a non-equi-join.

+ is a string concatenation operator in SQL Server. String concatenation is discussed in detail in the next chapter.

This query produces the 70 rows of output (of which we show a sample):

SENIORITY
-------------------------------------------------------------
Mary is in a higher class than Susan
Kelly is in a higher class than Susan
Donald is in a higher class than Susan
Chris is in a higher class than Susan
Jake is in a higher class than Susan
Holly is in a higher class than Susan
Jerry is in a higher class than Susan
Harrison is in a higher class than Susan
Francis is in a higher class than Susan
Benny is in a higher class than Susan
Mary is in a higher class than Monica
Kelly is in a higher class than Monica
Donald is in a higher class than Monica
.
.
.
Mary is in a higher class than Phoebe
Kelly is in a higher class than Phoebe
Donald is in a higher class than Phoebe
.
.
.
Mary is in a higher class than Rachel
Kelly is in a higher class than Rachel
Donald is in a higher class than Rachel
.
.
.
Mary is in a higher class than Cramer
Kelly is in a higher class than Cramer
Donald is in a higher class than Cramer
.
.
.
(70 row(s) affected)

In this join, all the rows where x.class is greater than y.class (which is restricted to 3) are joined to the rows that have y.class = 3. So Mary, the first row that has x.class = 4, is joined to the first row where class = 3 (y.class = 3), which is Susan. Then, the next row in the Student table with x.class = 4 is Kelly, so Kelly is joined to Susan (y.class = 3), etc.

To more fully understand how the self join is working, view the data in the Student table.

The alternative INNER JOIN syntax for this non-equi-join is:

SELECT 'SENIORITY' = x.sname + ' is more senior than ' + y.sname
FROM Student AS x INNER JOIN Student AS y
ON x.class > y.class
WHERE y.class = 3