Using Subqueries as an Alternative to Multiple Queries

3 4

Subqueries are often overlooked because in most cases nested queries are more popular and easier for the SQL novice to create. Any time you base one query on another, you're dealing with a nested query, and often that's the appropriate choice. On the other hand, a nested query can be slow. You might want to try replacing a nested query with a subquery to compare performance. However, subqueries are not necessarily superior to nested queries and can actually slow things down. There's no real tried-and-true rule of thumb you can use for guidance. Being aware that you have an alternative is probably the best guideline.

The next time you're dealing with a nested query, try to rewrite it as a subquery and see whether there's any improvement in performance. Unless you're dealing with a particularly complex set of criteria or a huge amount of data, you probably won't notice much difference, but it's worth a try.

To create a subquery from a nested query, follow these steps:

1. Open the base query in the SQL window, and copy it to the Clipboard.
2. Open the second query in the SQL window, and paste the first SELECT statement into the second. Knowing just where to paste can be a bit of a challenge, but generally you'll position the statement in a WHERE clause.
3. Replace references to the first query in the second query's original SELECT statement.
4. Run the query.

Most of the time, Jet can handle the subquery. When it can't, you can try rewriting the subquery or abandon the effort.

Now let's take a look at a nested query and try rewriting it as a subquery. Suppose you want to find the oldest order in the Northwind Orders table. You also want to identify that order by customer. You can use the Min aggregate function to return the oldest order, but the minute you add the Customer field to the mix, you change the dynamics of the group, and the query returns a record for every customer with a different order date.

To solve the problem using a nested query, follow these steps:

1. Create a Totals query on the Orders table by selecting the Min summary function in the Total cell of the OrderDate field, as shown in Figure 11-25. Save the query. (In this example, the query is named qryMax1.) If you run the query, you'll see it returns one record with the date 7/4/96.

   

   Figure 11-25. Use the Min aggregate function to return the oldest order.

   For more information about aggregates, see Chapter 9, "Using Queries to Select Data."

2. Base a second query on the first (qryMax1) by selecting qryMax1 in the Database window, selecting Query from the New Object button's drop-down list, and then clicking OK in the New Query dialog box.
3. Click the Show Table button on the Query Design toolbar to open the Show Table dialog box, and add the Orders table to the query design window.
4. Drag the MinOfOrderDate field to the OrderDate field in the Orders field list to create a relationship between the two data sources.
5. Add the CustomerID field from the Orders table and the MinOfOrderDate field from the first query, as shown in Figure 11-26.

   

   Figure 11-26. Base a second query on the first query.

6. Run the query to return the results shown in Figure 11-27. Save the query as qryMax2.

   

   Figure 11-27. The nested query returns the oldest order and the name of the customer who placed it.

Let's review the two SQL statements for both queries. The first (qryMax1) query's statement looks like this:

 SELECT Min(Orders.OrderDate) AS MinOfOrderDate FROM Orders; 

A simple SELECT statement returns the minimum value from the OrderDate field from the Orders table. The second query looks like this:

 SELECT Orders.CustomerID, qryMax1.MinOfOrderDate FROM qryMax1  INNER JOIN Orders ON qryMax1.MinOfOrderDate = Orders.OrderDate; 

This time there are two data sources: qryMax1 and Orders are related on the OrderDate and MinOfOrderDate fields. If the two entries match, the query will return the CustomerID and the OrderDate value for that record.

The alternative subquery looks like this:

 SELECT Orders.CustomerID, Orders.OrderDate FROM Orders WHERE (((Orders.OrderDate) In (SELECT Min(Orders.OrderDate)  As OrderDate FROM Orders))); 

The WHERE clause replaces the JOIN. Because you're working only with the Orders table, references to qryMax1 need to be restated in terms of the Orders table, as follows:

• Replace qryMax1.MinOfOrderDate with Orders.OrderDate in the initial SELECT statement.
• Replace qryMax1 with Orders in the FROM clause.
• Replace INNER JOIN Orders ON qryMax1.MinOfOrderDate = Orders.OrderDate with the WHERE clause shown in the preceding subquery.

If the transition seems complicated, don't worry. Once you've actually created a subquery from a nested query a few times, you'll get the hang of it.