Subqueries with Aliases

Many statements in which the subquery and the outer query refer to the same table can also be stated as self-joins (joining a table to itself). For example, you can find authors who live in the same city as Livia Karsen by using a subquery:

SELECT au_lname, au_fname, city
FROM authors
WHERE city IN
    (SELECT city
    FROM authors
    WHERE au_fname = 'Livia'
        AND au_lname = 'Karsen')

au_lname

au_fname

city

----------

---------

-------

Green

Marjorie

Oakland

Straight

Dean

Oakland

Stringer

Dirk

Oakland

MacFeather

Stearns

Oakland

Karsen

Livia

Oakland

(5 row(s) affected)

Or you can use a self-join:

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'

From the earlier discussion of self-joins, you might recall that table aliases are required because the table being joined to itself appears in two different roles. Aliases can also be used in nested queries that refer to the same table in an inner and outer query:

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')

Explicit aliases make it clear that the reference to authors in the subquery does not mean the same thing as the reference in the outer query.