Multiple Levels of Nesting

A subquery can itself include one or more subqueries. Any number of subqueries can be nested in a statement.

This query finds the names of authors who have participated in writing at least one popular computer book.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
   (SELECT au_id
   FROM titleauthor
   WHERE title_id IN
      (SELECT title_id
      FROM titles
      WHERE type = 'popular_comp'))

Here is the result set:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
Carson                                   Cheryl               
Dull                                     Ann                  
Locksley                                 Charlene             
Hunter                                   Sheryl               

(4 row(s) affected)

The innermost query returns the title ID numbers PC1035, PC8888, and PC9999. The query at the next higher level is evaluated with these title IDs and returns the author ID numbers. Finally, the outer query uses the author IDs to find the names of the authors.

You can also express this query as a join:

USE pubs
SELECT au_lname, au_fname
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
   JOIN titles ON titleauthor.title_id = titles.title_id
WHERE type = 'popular_comp'