Correlated Subqueries with Aliases

You can use a correlated subquery to find the types of books that are published by more than one publisher. Aliases are required to distinguish the two different roles in which the titles table appears:

SELECT DISTINCT t1.type
FROM titles t1
WHERE t1.type in
    (SELECT t2.type
    FROM titles t2
    WHERE t1.pub_id <> t2.pub_id)

type
----------
business
psychology

(2 row(s) affected)

The preceding nested query is equivalent to this self-join:

SELECT DISTINCT t1.type
FROM titles t1, titles t2
WHERE t1.type = t2.type
    AND t1.pub_id <> t2.pub_id