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.”
SQLselect 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 next 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:
SQLselect au_lname, au_fname from authors, titles, titleauthors where authors.au_id = titleauthors.au_id and titles.title_id = titleauthors.title_id and type = 'popular_comp'
3.128.198.36