SubQueries to the Rescue

<< Click to Display Table of Contents >>

Navigation:  Queries >

SubQueries to the Rescue

Peter Vogel

 

One of SQL's more useful features is its subqueries. Peter Vogel hunts them down for you in this article and shows how to get some enormous performance gains. He even bags the rare and powerful correlated subquery.

 

In an earlier article of my SQL series, I mentioned that Access doesn't directly support one of SQL's more powerful features: subqueries. In this article I'll defend that point of view because, as it has been pointed out to me, you can create subqueries in the Access query design window. I'll also show you how to use this feature to speed up your programs while eliminating code.

 

First, some background: A subquery is literally a query within a query. Using subqueries allows you to perform some action using another query as part of your Where clause. For instance, you might want a list of all of the employees in every department that exceeded its production quota. To do this in Access, you might first create the Select query qryGetDepts to find all the departments:

 

Select DeptName

From Departments

Where DeptActual > DeptTarget

 

You might then go on to use qryGetDepts as part of another query to find all the employees:

 

Select *

From Employee Inner Join qryGetDept

On EmpDept = DeptName

 

 

By using a subquery in the Where clause of the Employee query, you can answer this question in just one query:

 

Select *

From Employees 

Where EmpDept in

(Select DeptName

From Departments

Where DeptActual > DeptTarget)

 

 

The In key word in the Where clause links the main query (Select * from Employees) to the subquery (Select DeptName from Departments). In this example, the subquery generates a pool of Department names that is used to control which Employee records are used.

 

Unfortunately, to create a subquery in Access you must type the entire subquery into one of the cells in the Where row of the query grid. Compare this to the elegant way that the query design window allows you to specify joins between tables by dragging and dropping fields from one table to another. With that comparison in mind, you can understand why I say that, while Access may allow subqueries, it doesn't support them. A graphical solution, for instance, might allow you to open a new window in the query grid and use that to create your subquery.

 

Existential queries

The In key word isn't the only one you can use to link a subquery to a Where clause. For instance, imagine that the branch office is entitled to a bonus if even one department exceeds its quota:

 

Select Revenue * Bonus

From CompanyRevenue 

Where Exists

(Select DeptName

From Departments

Where DeptActual > DeptTarget)

 

The Exists key word says that, if the subquery returns even one record, the Where condition is satisfied. You can also use Not Exists when you want to verify that nothing happened.

 

In many ways, a subquery functions like a variable. For instance, a subquery that's guaranteed to return just one value can be used in a comparison anywhere you can use a constant. This query, for example, finds the employees whose salaries are greater than the highest salary allowed:

 

Select *

From Employees

Where Salary >

(Select Max(Salary)

From SalaryTable)

 

 

By using the Any or All key words in a comparison, you can test against a set of records. For example, by using All, this query finds the departments that did better than all the other departments did last year:

 

Select DeptName

From Departments

Where DeptActual > All

(Select DeptActual

From Departments

Where ResultYear = #1996#)

 

This query, on the other hand, finds the departments that did better than any other department:

 

Select DeptName

From Departments

Where DeptActual > Any

(Select DeptActual

From Departments)

 

If you've read my previous articles, you'll know that performance is important to me. I ran some time trials to see which method (using subqueries or a query within a query) gave the fastest response. One test retrieved the maximum value from a table of 37,000 records and then retrieved all the records larger than that value from another table with 66,000 records. The two-query method ran in 150 seconds. The subquery method ran in less than two seconds. Similar comparisons between multiple queries and subqueries showed the same kind of results.

 

The rare correlation

I've said before that using a SQL statement is almost always faster than using a bunch of procedural code. I've also suggested that anything that can be done with procedural code can be done with one or more queries. SQL's correlated subqueries make that claim possible.

 

On one occasion I needed to find all the employees whose salary exceeded the maximum for their pay level. My first inclination was to do this procedurally: read the first employee record, pull the salary range for the employee, do the check, and go on to the next employee. But, by using a correlated subquery, I was able to harness the speed and power of SQL to do the same thing. The correlated subquery allowed me to pass data from the main query to the subquery to control what data was retrieved:

 

Select *

From Employees E

Where Salary >

(Select MaxSalary

From SalaryTable S

Where S.PayLevel = E.PayLevel)

 

 

The "From Employees E" clause assigns the alias E to the table Employees. Similarly, the alias S is assigned to the SalaryTable in the subquery. The Where clause ensures that the MaxSalary field is retrieved from the SalaryTable only when the PayLevel of the SalaryTable (S.PayLevel) matches the PayLevel from the Employee table in the main query (E.PayLevel).

 

While I've used Select queries in my previous examples, a subquery also can be used in the Where clause for Updates, Deletes, and Inserts. If I wanted to give a 10 percent raise to the employees with the lowest salary in each department, I'd use this:

 

Update Employees E

Set Salary = Salary * 1.10

Where Salary = 

(Select Min(Salary)

From Employee F

Where F.Dept = E.Dept)

 

 

The ability to eliminate procedural code, in addition to the possibility of significantly speeding up processing, should make subqueries an important part of your SQL toolkit.

 

From Garry:
 
I tried this on a single table to see if I could return the lowest chargeable rate for each project that we work on.

SELECT p.[project], p.person, p.chargerate FROM participants AS p

WHERE (((p.[chargerate])=(select min(chargerate) from participants q where p.[project-id] = q.[project-id] and q.chargerate > 0)));

This looks like this in the query window

 

 

199707_pv_subqueries

 

 

So long, folks

This is the last of my series of articles on SQL. I've enjoyed doing them but, more importantly, I hope I've added some new tools to your workbench. A tremendous amount of power is available in SQL if you know where to look for it. Ideally, these articles have made you say "I didn't know that" or, more importantly, "I can use that!" The nice thing about using SQL is that it doesn't just work in Access. Like good manners, SQL will take you anywhere.

 

Earlier articles in this series are here
Jun-1997  
SQL Without Joins  
Is SQL Better than DAO for Creating Tables and Indexes

 

or try Access Subquery Techniques