Self-Joins and Correlation Names

Joins that compare values within the same column of one table are called self-joins. To distinguish the two roles in which the table appears, use aliases, or correlation names.

For example, you can use a self-join to find out which authors in Oakland, California, live in the same postal code area. Since this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you can temporarily and arbitrarily give the authors table two different correlation names—such as au1 and au2—in the from clause. These correlation names qualify the column names in the rest of the query. The self-join statement looks like this:

select au1.au_fname, au1.au_lname, 
au2.au_fname, au2.au_lname 
from authors au1, authors au2 
where au1.city = "Oakland" and au2.city = "Oakland" 
and au1.state = "CA" and au2.state = "CA" 
and au1.postalcode = au2.postalcode

au_fname     au_lname    au_fname    au_lname 
---------    ----------- --------    -------- 
Marjorie     Green       Marjorie    Green 
Dick         Straight    Dick        Straight 
Dick         Straight    Dirk        Stringer 
Dick         Straight    Livia       Karsen 
Dirk         Stringer    Dick        Straight 
Dirk         Stringer    Dirk        Stringer 
Dirk         Stringer    Livia       Karsen 
Stearns      MacFeather  Stearns     MacFeather 
Livia        Karsen      Dick        Straight 
Livia        Karsen      Dirk        Stringer 
Livia        Karsen      Livia       Karsen 
 
(11 rows affected)

List the aliases in the from clause in the same order as you refer to them in the select list, as in this example. Depending on the query, the results may be ambiguous if you list them in a different order.

To eliminate the rows in the results where the authors match themselves, and are identical except that the order of the authors is reversed, you can make this addition to the self-join query:

select au1.au_fname, au1.au_lname, 
au2.au_fname, au2.au_lname 
from authors au1, authors au2 
where au1.city = "Oakland" and au2.city = "Oakland" 
and au1.state = "CA" and au2.state = "CA" 
and au1.postalcode = au2.postalcode 
and au1.au_id < au2.au_id

au_fname    au_lname     au_fname    au_lname 
---------   -----------  ---------   --------- 
Dick        Straight     Dirk        Stringer 
Dick        Straight     Livia       Karsen 
Dirk        Stringer     Livia       Karsen 
 
(3 rows affected)

It is now clear that Dick Straight, Dirk Stringer, and Livia Karsen all have the same postal code.