A subquery is essentially a query within a query. A subquery is a SELECT within a select (also called nested, sub, and inner). When the SELECT is executed, the inner SELECT is executed and the results are used to resolve the outer SELECT. Subqueries can be based on multiple tables where the result of the query on one table are used to extract data from another query. Subqueries can also be based on the same table when you want to select data in the table based on an initial conditional check of the table. The subquery can be located in a FROM, WHERE or HAVING clause and can use the tests of >, <, = , !=, >=, <=, ANY, ALL, IN. The structure of the subquery calls for the inner query which is resolved first to be enclosed in parenthesis. It is also important to note that the value returned from the inner select is the value that the outer select is testing on - the code must reflect this fact. The examples below show a variety of uses of the select within a select.

In the first example, the inner select will return the DyrFirst for John Doe. Note that the way this subquery is constructred it would not work if there were multiple people in the table with the last name of Doe - more about that in the next example.

The inner select finds the person with the name of John Doe and returns the 1995 which is the date of the first contribution (DYRFirst). Then the outer select shows the fields asked for where the DyrFirst on the record is greater than the 1995 that was returned from the inner query.

SELECT DIdno, DName, DYrFirst, Dcontact
FROM newdonor
WHERE DYrFirst >
   (SELECT DYrFirst
    FROM newdonor
    WHERE DName LIKE '*Doe');

The table below shows the entire newdonor table for comparison purposes.

For the following query, I used the newdonor table after I had added a record to it. The table as it now appears is shown below:

This select statement resolves the inner select first by coming back with the DyrFirst for all donors that have a contact person of Ann Smith. Because multiple years will be returned, I need to use the IN when I code the WHERE clause as opposed to the greater than sign that I used in the previous code.

The inner select will return 1995 and 1994.

The outer select then returns the records where the date on the record matches one of these two numbers. The results are shown below.

SELECT DIdno, DName, DYrFirst, DContact
FROM newdonor
WHERE DYrFirst IN    
         (SELECT DYrFirst      
          FROM newdonor     
          WHERE DContact = 'Ann Smith');

In this example, the inner query returns the average contribution amount. The outer query than shows all of the donations that were greater than the average. The average is 665 so the results will show all donations above that amount.

SELECT DIdno, DDriveNo, DAmtCont
FROM newdonation
WHERE DAmtCont >=
    (SELECT AVG(DAmtCont)
     FROM newdonation);

In the next example, I am going to use two tables. One in the inner select or subquery and one in the outer select. In this example, the inner select returns the identification numbers (Didno) from the newdonation table for all donors who gave a contribution greater than or equal to 1000. Then the outer select showed the identification number, name and state for the donors with that identification number.

SELECT DIdno, DName, DState
FROM newdonor
WHERE DIdno IN
    (SELECT DIdno
     FROM newdonation
     WHERE DAmtCont >= 1000);

To prove that this worked, I will show the donation table below. The donor table appears in the example above.

Looking at this table we can see that the donations greater than or equal to 1000 were given by donor 11111, 33333, and 44444. Donors 22222 and 55555 did not meet the criteria. The answer shows the requested information about these donors.

In this example, the inner select returns the average donation (which I calculated at 665).

I am now asking to see the driveno and the sum of the contributions for all drive groups where the sum is greater than the average. The sum for 125 is 350 so it does not return. The sum of drives 123 and 124 are both greater than the average, so they are listed in the results.

SELECT DDriveNo, SUM(DAmtCont) AS [Sum Contr]
FROM newdonation
GROUP BY DDriveNo
HAVING SUM(DAmtCont) >
    (SELECT AVG(DAmtCont)
     FROM newdonation);

As an aside, note that the name that I group by is listed in the outer select along with the results of a group function. If I had grouped by two things then both of those things could have been listed. If you group by two things, the first thing is the major grouping and the second thing is the minor grouping.
For the next examples I decided that I need more data in the new donation table. The results of these additions is the table shown below.

The next example has an AND in the WHERE clause so there are two inner queries or subqueries. In analizing the results, I will show the results of each of the inner queries below.

SELECT DIDno, DName, DCity, DState, DYrFirst, DContact
FROM newdonor
WHERE DYrFirst IN
    (SELECT DYrFirst
     FROM newdonor
     WHERE  DContact = "David Costa")
AND DState IN
    (SELECT DState
     FROM newdonor
     WHERE DCity = "Providence" OR DCity = "Fall River");

The first inner query returns 1996 or 1995 and the second select returns MA and RI. This means the outer query is return information where the year is 1995 or 1995 and the state is MA or RI. This would have been a better test if there was a state other than MA or RI in the table.

SELECT DYrFirst
FROM newdonor
WHERE  DContact = "David Costa";

SELECT DState
FROM newdonor
WHERE DCity = "Providence" OR DCity = "Fall River");

The next example uses a compound subquery with an OR.

SELECT DIDno, DName, DCity, DState, DYrFirst, DContact
FROM newdonor
WHERE DYrFirst IN
    (SELECT DYrFirst
     FROM newdonor
     WHERE  DContact = "Ann Smith")
OR DState IN
    (SELECT DState
     FROM newdonor
     WHERE DCity = "Providence");

To analyze this, I will look at each of the inner selects separately.

SELECT DYrFirst
FROM newdonor
WHERE  DContact = "Ann Smith";

SELECT DState
FROM newdonor
WHERE DCity = "Providence";

The results of the query are therefore all records that have a DYrFirst of 1994 or 1995 OR a DState of RI. Looking at the output, the only records that are 1996 are from RI so it worked!
This example uses ALL before the inner select.

SELECT DIdno, DDriveNo, DDateCont, DAmtCont
FROM newdonation
WHERE DAmtCont > ALL
    (SELECT MIN(DAmtCont)
     FROM newdonation
     GROUP BY DDriveNo);

In this example, the inner select comes up with the minimum contribution by driveno. This is shown as a separate query below.

SELECT MIN(DAmtCont)
FROM newdonation
GROUP BY DDriveNo;

Note that the minimums are 500, 500 and 100. The outer select then shows contributions that are greater than all of those minimums. Anything 500 or below does not show.
The next example uses the ANY instead of the ALL in the select.

SELECT DIdno, DDriveNo, DDateCont, DAmtCont
FROM newdonation
WHERE DAmtCont > ANY
    (SELECT MIN(DAmtCont)
     FROM newdonation
     GROUP BY DDriveNo);

Again the inner select returned 500, 500 and 100. The only contribution that was not greater than these was the 100 contribution.
In the example below, I modified it slight to remove the 100 from the list. After the groups were formed I excluded any group that had a minimum that wasn't greater than 250 before the numbers were returned to the outer select.

SELECT DIdno, DDriveNo, DDateCont, DAmtCont
FROM newdonation
WHERE DAmtCont > ANY
    (SELECT MIN(DAmtCont)
     FROM newdonation
     GROUP BY DDriveNo
     HAVING MIN(DAmtCont) > 250);

The results of this query show only contributions greater than the 500 that was returned.