MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 2
December 1, 2003
Introduction to Subqueries in MS Access
Subqueries provide us the capability of "embedding" a SELECT statement inside another SELECT statement. The result is that a query is filtered by the values in another query in the simplest form, but several subqueries can be nested into one SELECT statement. We generate queries to present only part of a set of data records, typically to perform analysis or to produce reports; a subquery allows us to filter that data even further.
We will examine the syntax of a basic subquery to gain an understanding of its components, and then we will build a subquery. Starting with a basic SELECT query, we will create a second SELECT query to meet an illustrative business need. The second query will "knit" the two queries together in a single, "consolidated" query. Our example will make it easier to understand how one query is modified by another to produce the results that information consumers require. We will accomplish this in multiple steps to illustrate the construction and use of the subquery in reaching our end objective.
As we noted in the introduction, a subquery is formed when we nest an SQL SELECT statement inside another SQL statement. Nesting can be within another SELECT statement or within other types of SQL statements, including:
Nesting can also be within another subquery, and numerous levels of "subnesting" are possible. A subquery typically acts as a substitute for an expression within a WHERE or HAVING clause, where the SELECT statement of the subquery generates a value set that is evaluated by the clause, or within the field list of a SELECT statement.
Although subqueries can be used in many ways, some of the basic syntactical concepts can be laid out with the following common example:
SELECT OrderID, Freight FROM Orders WHERE Freight < (SELECT AVG(Freight) FROM Orders) ORDER BY Freight DESC
The example SQL above contains the components described in Table 1:
Table 1: Subquery Syntax Components
With regard to the subquery component above,
SELECT AVG(Freight) FROM Orders
$ 78.24 was returned from my copy of the Northwind database. The example query, containing the primary query and subquery, delivers the dataset partially shown in Illustration 1.
Queries such as the example, where "above - and - below average" considerations come into play, are a good application, in many cases, for subqueries. The potential uses for, and operations of, subqueries in general also begin to appear for many who, previously unacquainted with subqueries, examine a use such as this. I use subqueries in scenarios that range from fairly straightforward, as above, to quite sophisticated. We will revisit subqueries in future articles, after we establish some fundamental concepts, and explore some of these uses.
Let's move into a hands-on illustration to reinforce our understanding of subqueries, using the Northwind sample database.