Subqueries with Aliases

Subqueries with Aliases

Many statements in which the subquery and the outer query refer to the same table can 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:

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

Here is the result set:

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:

USE pubs
SELECT au1.au_lname, au1.au_fname, au1.city
FROM authors AS au1 INNER JOIN authors AS au2 ON au1.city = au2.city
   AND au2.au_lname = 'Karsen'
   AND au2.au_fname = 'Livia'

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.

USE pubs
SELECT au1.au_lname, au1.au_fname, au1.city
FROM authors AS au1
WHERE au1.city in
   (SELECT au2.city
   FROM authors AS au2
   WHERE au2.au_fname = 'Livia'
      AND au2.au_lname = 'Karsen')

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