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 Author table with itself, the Author table appears in two roles. To distinguish these roles, you must give the Author table two different aliases (a1 and a2) 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:

SELECT a1.Firstname,
       a1.Lastname,
       a2.Firstname,
       a2.Lastname
FROM   Author a1
INNER JOIN Author a2
  ON   a1.Zip = a2.zip
WHERE  a1.CityId = 'Oakland'
AND    a1.StateId = 'CA'
ORDER BY a1.Firstname ASC,
       a1.Lastname ASC

Here is the result set:

Firstname  Lastname     Firstname  Lastname
---------- ------------ ---------- ------------
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:

SELECT a1.Firstname,
       a1.Lastname,
       a2.Firstname,
       a2.Lastname
FROM   Author a1
INNER JOIN Author a2
 ON   a1.Zip = a2.Zip
WHERE  a1.CityId = 'Oakland'
AND    a1.StateId = 'CA'
AND    a1.Id _a_m_p_;_lt; a2.Id
ORDER BY a1.Firstname ASC,
       a1.Lastname ASC

Here is the result set:

Firstname  Lastname     Firstname  Lastname
---------- ------------ ---------- ------------
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.