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'