SUBQUERIES:
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 quotes. 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, I want to find out what the year goal is for someone with the last name of Ash. Once
I find that out, I want to display everyone that has a year goal greater than the year goal of Ash. Notice
the structure: the inner select is retrieving a yrgoal and the outer select is testing against yrgoal.
SQL CODE:
SQL> SELECT idno, name, yrgoal, contact
2 FROM donor
3 WHERE yrgoal >
4 (SELECT yrgoal
5 FROM donor
6 WHERE name LIKE '%Ash');
IDNO NAME YRGOAL CONTACT
----- --------------- --------- ------------
11111 Stephen Daniels 500 John Smith
12121 Jennifer Ames 400 Susan Jones
In the second example, the inner select comes up with all of the drive numbers where the contribution amount
is greater than 20. The outer select shows information for all rows where the drive number is in the list of
drives that resulted from the inner select. It is important to note the use of IN in the outer select. The
inner select has a chance of returning more than one value and the outer select has to be able to deal with
multiple returns. A good way of handling this is with the IN.
SQL CODE:
SQL> SELECT idno, driveno, contdate, contamt
2 FROM donation
3 WHERE driveno IN
4 (SELECT driveno
5 FROM donation
6 WHERE contamt > 20);
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
23456 100 03-MAR-99 20
22222 100 14-MAR-99 10
12121 200 23-FEB-99 40
In the third example, the inner select finds the average yrgoal from the donor file and then the outer select
finds all donors where the yrgoal is greater than or equal to the average. Again the inner select is returning
the average for yrgoal and the outer select is testing against yrgoal.
SQL CODE:
SQL> SELECT name, yrgoal
2 FROM donor
3 WHERE yrgoal >=
4 (SELECT AVG(yrgoal)
5 FROM donor);
NAME YRGOAL
--------------- ---------
Stephen Daniels 500
Jennifer Ames 400
In the fourth example, I am using two tables. I am using the inner select to look at the donation table and
select all idno where the contamt is greater than 20. The outer select than prints the name and yrgoal from
the donor table for those donors that were selected by the inner select. Again, the inner select is coming up
with one or more idno and the outer select is testing for a idno in the list that was retrieved.
SQL CODE:
1 SELECT name, yrgoal
2 FROM donor
3 WHERE idno IN
4 (SELECT idno
5 FROM donation
6* WHERE contamt > 20)
SQL> /
NAME YRGOAL
--------------- ---------
Stephen Daniels 500
Jennifer Ames 400
In the fifth example, I am only using the donor table. In the inner select I am finding the average of yrgoal
and then in the outer select I am showing the groups by state and contact within state where the sum of the
yrgoal is greater than the average yrgoal.
SQL CODE:
SQL> SELECT state, contact, SUM(yrgoal)
2 FROM donor
3 GROUP BY state, contact
4 HAVING SUM(yrgoal) >
5 (SELECT AVG(yrgoal)
6 FROM donor);
ST CONTACT SUM(YRGOAL)
-- ------------ -----------
MA John Smith 500
RI Susan Jones 400
In the sixth example, I want to see information where two criteria are met. The criteria are in an AND
relationship. Each of the and relationships is an inner select. For clarity, I have put selects similar
to the inner selects as stand alone selects below. You will see that Stephen Daniels is the only person
that meets both of the AND criteria and is therefore the only one that the outer select displays.
SQL CODE:
1 SELECT name, city, state, yrgoal, contact, datefst
2 FROM donor
3 WHERE datefst IN
4 (SELECT datefst
5 FROM donor
6 WHERE yrgoal > 100)
7 AND state =
8 (SELECT state
9 FROM donor
10* WHERE city = 'Seekonk')
SQL> /
NAME CITY ST YRGOAL CONTACT DATEFST
--------------- ---------- -- --------- ------------ ---------
Stephen Daniels Seekonk MA 500 John Smith 03-JUL-98
The examples below are showing the inner select statements from above (I added name in the select to help
show which rows would be selected in each of the inner selects.
SQL CODE:
SQL> SELECT name, datefst
2 FROM donor
3 WHERE yrgoal > 100;
NAME DATEFST
--------------- ---------
Stephen Daniels 03-JUL-98
Jennifer Ames 24-MAY-97
SQL> SELECT name, state
2 FROM donor
3 WHERE city = 'Seekonk';
NAME ST
--------------- --
Stephen Daniels MA
SQL>
In the seventh example, I what to select all colums from donor in the outer select where the contact
is in the list that the first inner select comes up with OR the contact is in the list that the second
inner select comes up with. Again I showed the two inner selects separately below to show what contact
would be selected by each.
SQL CODE:
SQL> SELECT *
2 FROM donor
3 WHERE contact IN
4 (SELECT contact
5 FROM donor
6 WHERE state = 'RI')
7 OR
8 contact IN
9 (SELECT contact
10 FROM donor
11 WHERE yrgoal > 100);
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
The two selects below are the same as the inner selects in the example above.
SQL CODE:
SQL> SELECT contact
2 FROM donor
3 WHERE state = 'RI';
CONTACT
------------
Susan Jones
Susan Jones
SQL> SELECT contact
2 FROM donor
3 WHERE yrgoal > 100;
CONTACT
------------
John Smith
Susan Jones
SQL>
In the eighth example, I am using ALL in the outer select. The inner select comes back with the minimum
yrgoal grouped by state. The outer select then gives the information about any donor that is greater than
the minimum in ALL of the states.
SQL CODE:
1 SELECT *
2 FROM donor
3 WHERE yrgoal > ALL
4 (SELECT MIN(yrgoal)
5 FROM donor
6* GROUP BY state)
SQL> /
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- -----------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
In the ninth example, I am using ANY in the outer select. The inner select comes back with the minimum
yrgoal grouped by state. The outer select then gives the information about any donor that is greater
than the minimum of ANY one of the states.
SQL CODE:
SQL> SELECT *
2 FROM donor
3 WHERE yrgoal > ANY
4 (SELECT MIN(yrgoal)
5 FROM donor
6 GROUP BY state);
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa
In the tenth example, I am again using two tables. Here I am displaying all of the information from the
donation table where the contamt than any of the amounts selected by the inner select. The inner select
divides yrgoal by 3 and then takes the minimum of these for each state group.
SQL CODE:
1 SELECT *
2 FROM donation
3 WHERE contamt > ANY
4 (SELECT MIN(yrgoal/3)
5 FROM donor
6* GROUP BY state)
SQL> /
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20