Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Dec 1, 2003

MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 2

By William Pearson

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.



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM