Multiple Levels of Nesting

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

An example of a problem that can be solved using a statement with multiple levels of nested queries is, "Find the names of authors who have participated in writing at least one popular computer book."

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'))

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:

SELECT au_lname, au_fname
FROM authors, titles, titleauthor
WHERE authors.au_id = titleauthor.au_id
    AND titles.title_id = titleauthor.title_id
    AND type = 'popular_comp'