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:
- INSERT INTO
- SELECT INTO
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:
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.
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.