O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

HOW DEEP THE RABBIT HOLE GOES: NESTING SUBQUERIES

The subquery can host a subquery of its own, the sub-subquery can do the same, and the sub-sub-subquery…you get the picture. They are called nested subqueries: a query within a query within a query.

The following is an example of a nested query that tries to find information about books that we tagged as containing SQL references:

SELECT bk_id, bk_title FROM books 
    WHERE bk_id IN
        (SELECT bk_id FROM search_books 
            WHERE tag_id in 
               (SELECT tag_id FROM searchTags 
                  WHERE tag_value LIKE ‘SQL%’))
bk_id    bk_title
-------- ----------------------------------------------------
1        SQL Bible
2        Wiley Pathways: Introduction to Database Management
3        Microsoft SQL Server 2000 Weekend Crash Course
4        SQL Functions: Programmers Reference

(4 row(s) affected)

Let's unravel the three queries. A subquery executes first. That is, the innermost query executes first, and the statement works up from there. In the previous example, the following query was executed first, and it returned a list of tag IDs associated with the books that have a title starting with SQL:

SELECT tag_id FROM searchTags WHERE tag_value LIKE ‘SQL%’
tag_id
--------------------
1

(1 row(s) affected)

There is but one token (out of 28) which has “SQL” as its value. This could be verified by running a SELECT query against the SearchTags table. The next-level query now looks like this:

SELECT bk_id FROM search_books WHERE tag_id in (1)
bk_id
--------------------
1
 2 3 4 (4 row(s) affected) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required