Subqueries

A subquery is a SELECT statement that is nested within another SQL statement. Subqueries are executed prior to execution of the containing SQL statement, 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

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 non- correlated subquery.

Subqueries are useful because they allow comparisons to be made without changing the size of the result set.