Subqueries with correlation names

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.