Discovering SQL: A Hands-On Guide for Beginners

WHAT YOU DON'T KNOW MIGHT HELP YOU

A subquery is an answer to several questions rolled up into a single statement. It is a variation on the concept “I do not know, but I know someone who does.”

Subquery in the WHERE Clause

The subquery can be used almost anywhere in the SQL statement, but the most common use is in the WHERE clause.

To illustrate the concept, let's try to find what books we might have on the top (fifth) shelf in the bookcase: “The one in the living room to the right”:

SELECT bk_title, bk_publisher FROM books
    WHERE bk_id IN (SELECT fk_bk_loc FROM location
        WHERE loc_shelf = 5)
bk_title                                             bk_publisher
---------------------------------------------------- -------------------
SQL Bible                                             Wiley
Wiley Pathways: Introduction to Database Management   Wiley
Microsoft SQL Server 2000 Weekend Crash Course        Wiley
SQL Functions: Programmers Reference                  Wiley

All we knew in this case (no pun intended!) was that we wanted books stored on shelf #5, and the relation between the BOOKS and LOCATION tables specified by primary/foreign keys, respectively. The subquery was executed first; it returned all the values (book ID(s)) associated with shelf #5 (LOC_SHELF column) in the bookcase with the unwieldy name ‘The one in the living room to the right’:

SELECT fk_bk_loc FROM location WHERE loc_shelf = 5
fk_bk_loc
---------------
1
2
3
4

Then the outermost query matched the BK_ID values with the returned list and made the selection from BOOKS table.

A subquery is also called an inner query