Using Self-Joins

A table can be joined to itself in a self-join. For example, you can use a self-join to find out the authors in Oakland, California who live in the same zip code area.

Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases  (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:

USE pubs

SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname

FROM authors au1 INNER JOIN authors au2

    ON au1.zip = au2.zip

WHERE au1.city = 'Oakland'

ORDER BY au1.au_fname ASC, au1.au_lname ASC

Here is the result set:

au_fname             au_lname            au_fname             au_lname           

-------------------- ------------------- -------------------- ---------

Dean                 Straight            Dean                 Straight                                

Dean                 Straight            Dirk                 Stringer                                

Dean                 Straight            Livia                Karsen                                  

Dirk                 Stringer            Dean                 Straight                                

Dirk                 Stringer            Dirk                 Stringer                                

Dirk                 Stringer            Livia                Karsen                                  

Livia                Karsen              Dean                 Straight                                

Livia                Karsen              Dirk                 Stringer                                

Livia                Karsen              Livia                Karsen                                  

Marjorie             Green               Marjorie             Green                                   

Stearns              MacFeather          Stearns              MacFeather                              

 (11 row(s) affected)

To eliminate the rows in the results in which the authors match themselves and to eliminate rows that are identical, except the order of the authors is reversed, make this change to the Transact-SQL self-join query:

USE pubs

SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname

FROM authors au1 INNER JOIN authors au2

    ON  au1.zip = au2.zip

WHERE au1.city = 'Oakland'

    AND au1.state = 'CA'

    AND au1.au_id < au2.au_id

ORDER BY au1.au_lname ASC, au1.au_fname ASC

Here is the result set:

au_fname     au_lname          au_fname             au_lname                                

------------ ----------------- -------------------- --------------------

Dean         Straight          Dirk                 Stringer

Dean         Straight          Livia                Karsen

Dirk         Stringer          Livia                Karsen

(3 row(s) affected)

It is now clear that Dean Straight, Dirk Stringer, and Livia Karsen all have the same zip code and live in Oakland, California.

See Also