VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources] نسخه متنی

Understanding Subqueries

This section defines some terms and introduces subqueries by giving an example of a SELECT statement that contains a simple subquery. Subsequent sections explain the types of subqueries and their syntax and semantics.

Suppose that you want to list the names of the publishers of biographies. The naive approach is to write two queries: one query to retrieve the IDs of all the biography publishers (

Eliminating Duplicate Rows with DISTINCT" in Chapter 4.

pub_id
------
P01 
P03 
P01 
P01 

Figure 8.2. Result of Listing 8.2.

pub_name
-------------------
Abatis Publishers
Schadenfreude Press

A better way is to use an inner join (

Creating an Inner Join with INNER JOIN" in Chapter 7.

Figure 8.3. Result of Listing 8.3.

pub_name
-------------------
Abatis Publishers
Schadenfreude Press

Another alternative is to use a subquery (

Listing 8.4 and

Figure 8.4 ). The subquery in Listing 8.4 is shown in red. A subquery also is called an

inner query, and the statement containing a subquery is called an

outer query. In other words, an enclosed subquery is an inner query of an outer query. Remember that a subquery can be nested in another subquery, so

inner and

outer are relative terms in statements with multiple nested subqueries.

Figure 8.4. Result of Listing 8.4.

pub_name
-------------------
Abatis Publishers
Schadenfreude Press

Simple and Correlated Subqueries" later in this chapter, but for now, all that you need to know is that in List Filtering with IN" in Chapter 4. Note that the inner query in Subqueries vs. Joins" later in this chapter).