This chapter has described subqueries, which allow you to use the results of one query to help define another query. Before moving on to the advanced query facilities of the SQL2 specification, let’s summarize subqueries:
- A subquery is a “query within a query.” Subqueries appear within one of the subquery search conditions in the WHERE or HAVING
- When a subquery appears in the WHERE clause, the results of the subquery are used to select the individual rows that contribute data to the query results.
- When a subquery appears in the HAVING clause, the results of the subquery are used to select the row groups that contribute data to the query results.
- Subqueries can be nested within other subqueries.
- The subquery form of the comparison test uses one of the simple comparison operators to compare a test value to the single value returned by a subquery.
- The subquery form of the set membership test (IN) matches a test value to the set of values returned by a subquery.
- The existence test (EXISTS) checks whether a subquery returns any values.
- The quantified tests (ANY and ALL) use one of the simple comparison operators to compare a test value to all of the values returned by a subquery, checking to see whether the comparison holds for some or all of the values.
- A subquery may include an outer reference to a table in any of the queries that contain it, linking the subquery to the current row of that query.
Figure 9-8 shows the final version of the rules for SQL query processing, extended to include subqueries. It provides a complete definition of the query results produced by a SELECT statement.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.