Table correlation names are required in self-joins because the table being joined to itself appears in two different roles (see Chapter 4, “Joins: Retrieving Data from Several Tables”). You can use correlation names in nested queries that refer to the same table in both an inner query and an outer query.
For example, to find authors who live in the same city as Livia Karsen:
select au1.au_lname, au1.au_fname, au1.city from authors au1 where au1.city in (select au2.city from authors au2 where au2.au_fname = "Livia" and au2.au_lname = "Karsen")
au_lname au_fname city ----------- --------- ------- Green Marjorie Oakland Straight Dick Oakland Stringer Dirk Oakland MacFeather Stearns Oakland Karsen Livia Oakland
Explicit correlation names make it clear that the reference to authors in the subquery is not the same as the reference to authors in the outer query.
Without explicit correlation, the subquery is:
select au_lname, au_fname, city from authors where city in (select city from authors where au_fname = "Livia" and au_lname = "Karsen")
Alternatively, state the above query, as well as other statements in which the subquery and the outer query refer to the same table, as self-joins:
select au1.au_lname, au1.au_fname, au1.city from authors au1, authors au2 where au1.city = au2.city and au2.au_lname = "Karsen" and au2.au_fname = "Livia"
A subquery restated as a join may not return the results in the same order; additionally, the join may require the distinct keyword to eliminate duplicates.
