Chapter 5: Subqueries: Using Queries Within Other Queries

Multiple levels of nesting

A subquery can include one or more subqueries. You can nest up to 50 subqueries in a statement.

For example, to find the names of authors who have participated in writing at least one popular computing book, enter:

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               Chastity 
Hunter                 Sheryl 
 
(4 rows affected)

The outermost query selects all author names. The next query finds the authors’ IDs, and the innermost query returns the title ID numbers PC1035, PC8888, and PC9999.

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"

Subqueries in update, delete, and insert statements

View this book as PDF