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.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.