Using Self-Joins

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

WHERE

SELECT

Operators

SELECT Examples

Using Operators in Expressions