Hands on: Using SQL sub-queries

This part in our occasional SQL series introduces sub-queries.

Understanding sub-queries gives you a useful and adaptable technique for writing queries.

The WHERE clause, covered here, lets you select rows that meet certain conditions.

Sub-queries let you add further refinement to the selection. A sub-query is just an SQL statement that would run on its own but is nested within another SQL statement. Here’s an example:

SELECT FirstName, LastName, Dept
FROM SalesStaff
WHERE SPID IN
(SELECT SPID
FROM SalesStaff
WHERE Dept = ‘Horticulture’) ;

This is in the sample file DBCJuly08.MDB, which you can find here.

The file contains all the queries described in this column and each has a number for easy identification, for example Q02, shown here in brackets after the relevant query.

An SQL statement can have many sub-queries within it, which brings us into the realm of complex nested SQL. A great deal of complexity can be introduced, and long, complicated code can be (and has been) written.

In my opinion, very complex SQL is often undesirable for two reasons. First, it makes the code unreadable for anyone who has to understand, maintain or edit it later. Second, the query may run slowly. This is because the query optimiser can find it difficult to identify an optimised pathway through the operations contained in convoluted code. The result is that the query runs sluggishly.

Both of these issues are addressed by writing a series of simpler SQL statements with one calling another. Not only is it easier for human beings to see what the code is doing, debug it and edit it if necessary, but these simple statements won’t give the optimiser indigestion and the queries will fly.

This is a complex area and some people write very clever, complex SQL statements that are faster than a series of smaller ones. But such people are relatively rare.

People learn SQL more easily when it’s split into smaller chunks, making me more of a fan of this approach. Let’s write a sub-query: DBCJuly08.MDB is available in two versions ­ the Access 2007 version is called DBCJul08-07.MDB.

The first thing to note is that the sub-query is wrapped in brackets (the number refers to the query name in the sample database):

(SELECT SPID
FROM SalesStaff
WHERE Dept = ‘Horticulture’) ;
(Q02)