Chapter 5: Subqueries: Using Queries Within Other Queries

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 
 
(2 rows affected)

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

Correlated subqueries with comparison operators

View this book as PDF