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.