Self-Joins and Aliases

You compare values within a column of a table with a self-join. For example, you can use a self-join to find out which authors in Oakland, California live in the same zip 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 aliases ¾ au1 and au2 ¾ in the FROM clause. These aliases are used to 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 au1.state = 'CA'
    AND au1.zip = au2.zip

au_fname	au_lname	au_fname	au_lname
--------	--------	--------	--------
Marjorie	Green	Marjorie	Green
Dean	Straight	Dean	Straight
Dean	Straight	Dirk	Stringer
Dean	Straight	Livia	Karsen
Dirk	Stringer	Dean	Straight
Dirk	Stringer	Dirk	Stringer
Dirk	Stringer	Livia	Karsen
Stearns	MacFeather	Stearns	MacFeather
Livia	Karsen	Dean	Straight
Livia	Karsen	Dirk	Stringer
Livia	Karsen	Livia	Karsen

(11 row(s) affected)

To eliminate the rows in the results where the authors match themselves and to eliminate rows that are identical except that the order of the authors is reversed, make this change 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 au1.state = 'CA'
    AND au1.zip = au2.zip
    AND au1.au_id < au2.au_id

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.