5.1 What Is a Subquery?

As we mentioned in Chapter 1, a subquery is a SELECT statement that is nested within another SQL statement. For the purpose of this discussion, we will call the SQL statement that contains a subquery the containing statement. Subqueries are executed prior to execution of their containing SQL statement (see Section 5.3 later in this chapter for the exception to this rule), and the result set generated by a subquery is discarded after its containing SQL statement has finished execution. Thus, a subquery can be thought of as a temporary table with statement scope.

Syntactically, subqueries are enclosed within parentheses. For example, the following SELECT statement contains a simple subquery in its WHERE clause:

SELECT * FROM customer 

WHERE cust_nbr = (SELECT 123 FROM dual);

The subquery in this statement is absurdly simple, and completely unnecessary, but it does serve to illustrate a point. When this statement is executed, the subquery is evaluated first. The result of that subquery then becomes a value in the WHERE clause expression:

SELECT * FROM customer 

WHERE cust_nbr = 123;

With the subquery out of the way, the containing query can now be evaluated. In this case, it would bring back information about customer number 123.

Subqueries are most often found in the WHERE clause of a SELECT, UPDATE, or DELETE statement, as well as in the SET clause of an UPDATE statement. A subquery may either be correlated with its containing SQL statement, meaning that it references one or more columns from the containing statement, or it might reference nothing outside itself, in which case it is called a noncorrelated subquery. A less commonly used but powerful variety of subquery, called the inline view, occurs in the FROM clause of a SELECT statement. Inline views are always noncorrelated; they are evaluated first and behave like unindexed tables cached in memory for the remainder of the query.