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.
Discussion
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:
- INSERT INTO
- SELECT INTO
- UPDATE
- DELETE
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.
Syntax
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:
|
Component
|
Description
|
|
Primary Query
|
SELECT OrderID, Freight FROM Orders
WHERE Freight < [VALUE]
ORDER BY Freight DESC
The primary query in the example is a
simple SELECT statement whose output would be a list of Orders (by OrderID)
whose Freight cost was less than a given VALUE. In our
example, we substitute the VALUE with the subquery below.
We order by the Freight cost
as a means of aligning our amounts for instant visual verification that the
results delivered appear correct.
|
|
Subquery
|
SELECT AVG(Freight) FROM Orders
Another simple SELECT statement,
whose output is a single value, the average Freight cost of all
records in the Orders table.
|
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.
Illustration 1: Example Query Results (Partial View)
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.
» 
Comment and Contribute
