Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Subqueries in Multiple Levels of Nesting

A subquery can itself include one or more subqueries. You can nest any number of subqueries.

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 computing book.”

SQL

select au_lname, au_fname
from authors
where au_id in
					  (select au_id
					   from titleauthors
					   where title_id in
					      (select title_id
					       from titles
					       where type = 'popular_comp') )
au_lname                               au_fname
====================================== =================
Carson                                 Cheryl
Dull                                   Ann
Hunter                                 Sheryl
Locksley                               Chastity
(4 rows affected)

The innermost query returns the title ID numbers PC1035, PC8888, and PC9999. The query at the ...