Correlated subqueries can be used in operations such as selecting data from a table referenced in the outer query. In this case a table alias (also called a correlation name) must be used to unambiguously specify which table reference to use. For example, you can use a correlated subquery to find the types of books published by more than one publisher. Aliases are required to distinguish the two different roles in which the titles table appears.
USE pubs
SELECT DISTINCT t1.type
FROM titles t1
WHERE t1.type IN
(SELECT t2.type
FROM titles t2
WHERE t1.pub_id <> t2.pub_id)
Here is the result set:
type
----------
business
psychology
(2 row(s) affected)
The preceding nested query is equivalent to this self-join:
USE pubs
SELECT DISTINCT t1.type
FROM titles t1 INNER JOIN titles t2 ON t1.type = t2.type
AND t1.pub_id <> t2.pub_id