5.1 What Is a Subquery?

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 the containing SQL statement (see Section 5.3 later in this chapter for the exception to this rule), and the result set generated by the subquery is discarded after the 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. 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.

Subqueries are useful because they allow comparisons to be made without changing the size of the result set. For example, we might want to find all customers that placed orders last month, but we might not want any given customer to be included more than once, regardless of the number of orders placed by that customer. Whereas joining the customer and orders tables would expand the result set by the number of orders placed by each customer, a subquery against the orders table using the IN or EXISTS operator would determine whether each customer placed an order, without regard for the number of orders placed.