Correlated subqueries with correlation names

You can use a correlated subquery to find the types of books that are published by more than one publisher:

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 

Correlation names are required in the following query to distinguish between the two roles in which the titles table appears. This nested query is equivalent to the self-join query:

select distinct t1.type 
from titles t1, titles t2 
where t1.type = t2.type 
and t1.pub_id != t2.pub_id