NOTE: The examples for today's lesson were created using Borland's ISQL, the same implementation used on Day 6, "Joining Tables." Remember, this implementation does not use the SQL> prompt or line numbers.
SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE)Notice how the second query is nested inside the first. Here's a real-world example that uses the PART and ORDERS tables:
SELECT * FROM PART
PARTNUM DESCRIPTION PRICE =========== ==================== =========== 54 PEDALS 54.25 42 SEATS 24.50 46 TIRES 15.25 23 MOUNTAIN BIKE 350.45 76 ROAD BIKE 530.00 10 TANDEM 1200.00
SELECT * FROM ORDERS ORDEREDON NAME PARTNUM QUANTITY REMARKS =========== ========== =========== =========== ======== 15-MAY-1996 TRUE WHEEL 23 6 PAID 19-MAY-1996 TRUE WHEEL 76 3 PAID 2-SEP-1996 TRUE WHEEL 10 1 PAID 30-JUN-1996 TRUE WHEEL 42 8 PAID 30-JUN-1996 BIKE SPEC 54 10 PAID 30-MAY-1996 BIKE SPEC 10 2 PAID 30-MAY-1996 BIKE SPEC 23 8 PAID 17-JAN-1996 BIKE SPEC 76 11 PAID 17-JAN-1996 LE SHOPPE 76 5 PAID 1-JUN-1996 LE SHOPPE 10 3 PAID 1-JUN-1996 AAA BIKE 10 1 PAID 1-JUL-1996 AAA BIKE 76 4 PAID 1-JUL-1996 AAA BIKE 46 14 PAID 11-JUL-1996 JACKS BIKE 76 14 PAID
SELECT * FROM ORDERS WHERE PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION LIKE "ROAD%") ORDEREDON NAME PARTNUM QUANTITY REMARKS =========== ========== =========== =========== ======== 19-MAY-1996 TRUE WHEEL 76 3 PAID 17-JAN-1996 BIKE SPEC 76 11 PAID 17-JAN-1996 LE SHOPPE 76 5 PAID 1-JUL-1996 AAA BIKE 76 4 PAID 11-JUL-1996 JACKS BIKE 76 14 PAID
SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION LIKE "ROAD%") ORDEREDON PARTNUM DESCRIPTION QUANTITY REMARKS =========== =========== ============ =========== ========= 19-MAY-1996 76 ROAD BIKE 3 PAID 1-JUL-1996 76 ROAD BIKE 4 PAID 17-JAN-1996 76 ROAD BIKE 5 PAID 17-JAN-1996 76 ROAD BIKE 11 PAID 11-JUL-1996 76 ROAD BIKE 14 PAID
SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS FROM ORDERS O, PART PHere you are using the aliases O and P for tables ORDERS and PART to select the five columns you are interested in. In this case the aliases were not necessary because each of the columns you asked to return is unique. However, it is easier to make a readable query now than to have to figure it out later. The first WHERE clause you encounter
WHERE O.PARTNUM = P.PARTNUMis standard language for the join of tables PART and ORDERS specified in the FROM clause. If you didn't use this WHERE clause, you would have all the possible row combinations of the two tables. The next section includes the subquery. The statement
AND O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION LIKE "ROAD%")adds the qualification that O.PARTNUM must be equal to the result of your simple subquery. The subquery is straightforward, finding all the part numbers that are LIKE "ROAD%". The use of LIKE was somewhat lazy, saving you the keystrokes required to type ROAD BIKE. However, it turns out you were lucky this time. What if someone in the Parts department had added a new part called ROADKILL? The revised PART table would look like this:
SELECT * FROM PART PARTNUM DESCRIPTION PRICE =========== ==================== =========== 54 PEDALS 54.25 42 SEATS 24.50 46 TIRES 15.25 23 MOUNTAIN BIKE 350.45 76 ROAD BIKE 530.00 10 TANDEM 1200.00 77 ROADKILL 7.99Suppose you are blissfully unaware of this change and try your query after this new product was added. If you enter this:
SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION LIKE "ROAD%")the SQL engine complains
multiple rows in singleton selectand you don't get any results. The response from your SQL engine may vary, but it still complains and returns nothing.
To find out why you get this undesirable result, assume the role of the SQL engine. You will probably evaluate the subquery first. You would return this:
SELECT PARTNUM FROM PART WHERE DESCRIPTION LIKE "ROAD%" PARTNUM =========== 76 77You would take this result and apply it to O.PARTNUM =, which is the step that causes the problem.
SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION = "ROAD BIKE") ORDEREDON PARTNUM DESCRIPTION QUANTITY REMARKS =========== =========== =============== =========== ========== 19-MAY-1996 76 ROAD BIKE 3 PAID 1-JUL-1996 76 ROAD BIKE 4 PAID 17-JAN-1996 76 ROAD BIKE 5 PAID 17-JAN-1996 76 ROAD BIKE 11 PAID 11-JUL-1996 76 ROAD BIKE 14 PAID
Avoiding the use of LIKE is a start. Another approach is to ensure the uniqueness of the search field during table design. If you are the untrusting type, you could use the method (described yesterday) for joining a table to itself to check a given field for uniqueness. If you design the table yourself (see Day 9, "Creating and Maintaining Tables") or trust the person who designed the table, you could require the column you are searching to have a unique value. You could also use a part of SQL that returns only one answer: the aggregate function.
SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM
AVG =========== 2419.16
SELECT O.NAME, O.ORDEREDON, O.QUANTITY * P.PRICE TOTAL FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.QUANTITY * P.PRICE > (SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM) NAME ORDEREDON TOTAL ========== =========== =========== LE SHOPPE 1-JUN-1996 3600.00 BIKE SPEC 30-MAY-1996 2803.60 LE SHOPPE 17-JAN-1996 2650.00 BIKE SPEC 17-JAN-1996 5830.00 JACKS BIKE 11-JUL-1996 7420.00
SELECT O.NAME, O.ORDEREDON, O.QUANTITY * P.PRICE TOTAL FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUMThese lines represent the common way of joining these two tables. This join is necessary because the price is in PART and the quantity is in ORDERS. The WHERE ensures that you examine only the join-formed rows that are related. You then add the subquery:
AND O.QUANTITY * P.PRICE > (SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM)The preceding condition compares the total of each order with the average you computed in the subquery. Note that the join in the subquery is required for the same reasons as in the main SELECT statement. This join is also constructed exactly the same way. There are no secret handshakes in subqueries; they have exactly the same syntax as a standalone query. In fact, most subqueries start out as standalone queries and are incorporated as subqueries after their results are tested.
Select * FROM SOMETHING WHERE ( SUBQUERY(SUBQUERY(SUBQUERY)));Subqueries can be nested as deeply as your implementation of SQL allows. For example, to send out special notices to customers who spend more than the average amount of money, you would combine the information in the table CUSTOMER
SELECT * FROM CUSTOMER
NAME ADDRESS STATE ZIP PHONE REMARKS ========== ========== ====== ========== =========== ========== TRUE WHEEL 55O HUSKER NE 58702 555-4545 NONE BIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE LE SHOPPE HOMETOWN KS 54678 555-1278 NONE AAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGR JACKS BIKE 24 EGLIN FL 34567 555-2314 NONEwith a slightly modified version of the query you used to find the above-average orders:
SELECT ALL C.NAME, C.ADDRESS, C.STATE,C.ZIP FROM CUSTOMER C WHERE C.NAME IN (SELECT O.NAME FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.QUANTITY * P.PRICE > (SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM)) NAME ADDRESS STATE ZIP ========== ========== ====== ========== BIKE SPEC CPT SHRIVE LA 45678 LE SHOPPE HOMETOWN KS 54678 JACKS BIKE 24 EGLIN FL 34567
SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUMThis result feeds into a slightly modified version of the SELECT clause you used before:
SELECT O.NAME FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.QUANTITY * P.PRICE > (...)Note the SELECT clause has been modified to return a single column, NAME, which, not so coincidentally, is common with the table CUSTOMER. Running this statement by itself you get:
SELECT O.NAME FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.QUANTITY * P.PRICE > (SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM) NAME ========== LE SHOPPE BIKE SPEC LE SHOPPE BIKE SPEC JACKS BIKE
You bring these results to the statement:
SELECT C.NAME, C.ADDRESS, C.STATE,C.ZIP FROM CUSTOMER C WHERE C.NAME IN
(...)
LE SHOPPE BIKE SPEC LE SHOPPE BIKE SPEC JACKS BIKEThis subquery provides the conditions that give you the mailing list:
NAME ADDRESS STATE ZIP ========== ========== ====== ====== BIKE SPEC CPT SHRIVE LA 45678 LE SHOPPE HOMETOWN KS 54678 JACKS BIKE 24 EGLIN FL 34567This use of IN is very common in subqueries. Because IN uses a set of values for its comparison, it does not cause the SQL engine to feel conflicted and inadequate.
Subqueries can also be used with GROUP BY and HAVING clauses. Examine the following query:
SELECT NAME, AVG(QUANTITY) FROM ORDERS GROUP BY NAME HAVING AVG(QUANTITY) > (SELECT AVG(QUANTITY) FROM ORDERS) NAME AVG ========== =========== BIKE SPEC 8 JACKS BIKE 14
SELECT AVG(QUANTITY) FROM ORDERS AVG =========== 6By itself, the query is as follows:
SELECT NAME, AVG(QUANTITY) FROM ORDERS GROUP BY NAME NAME AVG ========== =========== AAA BIKE 6 BIKE SPEC 8 JACKS BIKE 14 LE SHOPPE 4 TRUE WHEEL 5When combined through the HAVING clause, the subquery produces two rows that have above-average QUANTITY.
HAVING AVG(QUANTITY) > (SELECT AVG(QUANTITY) FROM ORDERS) NAME AVG ========== =========== BIKE SPEC 8 JACKS BIKE 14
SELECT * FROM ORDERS O WHERE 'ROAD BIKE' = (SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM)
ORDEREDON NAME PARTNUM QUANTITY REMARKS =========== ========== =========== =========== ========== 19-MAY-1996 TRUE WHEEL 76 3 PAID 17-JAN-1996 BIKE SPEC 76 11 PAID 17-JAN-1996 LE SHOPPE 76 5 PAID 1-JUL-1996 AAA BIKE 76 4 PAID
11-JUL-1996 JACKS BIKE 76 14 PAIDThis query actually resembles the following JOIN:
SELECT O.ORDEREDON, O.NAME, O.PARTNUM, O.QUANTITY, O.REMARKS FROM ORDERS O, PART P WHERE P.PARTNUM = O.PARTNUM AND P.DESCRIPTION = 'ROAD BIKE'
ORDEREDON NAME PARTNUM QUANTITY REMARKS =========== ========== =========== =========== ======= 19-MAY-1996 TRUE WHEEL 76 3 PAID 1-JUL-1996 AAA BIKE 76 4 PAID 17-JAN-1996 LE SHOPPE 76 5 PAID 17-JAN-1996 BIKE SPEC 76 11 PAID 11-JUL-1996 JACKS BIKE 76 14 PAID
WHERE P.PARTNUM = O.PARTNUMin which you compare P.PARTNUM, from the table inside your subquery, to O.PARTNUM, from the table outside your query. Because O.PARTNUM can have a different value for every row, the correlated subquery is executed for each row in the query. In the next example each row in the table ORDERS
SELECT * FROM ORDERS ORDEREDON NAME PARTNUM QUANTITY REMARKS =========== ========== =========== =========== ======= 15-MAY-1996 TRUE WHEEL 23 6 PAID 19-MAY-1996 TRUE WHEEL 76 3 PAID 2-SEP-1996 TRUE WHEEL 10 1 PAID 30-JUN-1996 TRUE WHEEL 42 8 PAID 30-JUN-1996 BIKE SPEC 54 10 PAID 30-MAY-1996 BIKE SPEC 10 2 PAID 30-MAY-1996 BIKE SPEC 23 8 PAID 17-JAN-1996 BIKE SPEC 76 11 PAID 17-JAN-1996 LE SHOPPE 76 5 PAID 1-JUN-1996 LE SHOPPE 10 3 PAID 1-JUN-1996 AAA BIKE 10 1 PAID 1-JUL-1996 AAA BIKE 76 4 PAID 1-JUL-1996 AAA BIKE 46 14 PAID 11-JUL-1996 JACKS BIKE 76 14 PAIDis processed against the subquery criteria:
SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM
WHERE 'ROAD BIKE' =Because each row is examined, the subquery in a correlated subquery can have more than one value. However, don't try to return multiple columns or columns that don't make sense in the context of the WHERE clause. The values returned still must match up against the operation specified in the WHERE clause. For example, in the query you just did, returning the PRICE to compare with ROAD BIKE would have the following result:
SELECT * FROM ORDERS O WHERE 'ROAD BIKE' = (SELECT PRICE FROM PART P WHERE P.PARTNUM = O.PARTNUM)
conversion error from string "ROAD BIKE"Here's another example of something not to do:
SELECT * FROM ORDERS O WHERE 'ROAD BIKE' = (SELECT * FROM PART P WHERE P.PARTNUM = O.PARTNUM)
Correlated subqueries can also be used with the GROUP BY and HAVING clauses. The following query uses a correlated subquery to find the average total order for a particular part and then applies that average value to filter the total order grouped by PARTNUM:
SELECT O.PARTNUM, SUM(O.QUANTITY*P.PRICE), COUNT(PARTNUM) FROM ORDERS O, PART P WHERE P.PARTNUM = O.PARTNUM GROUP BY O.PARTNUM HAVING SUM(O.QUANTITY*P.PRICE) > (SELECT AVG(O1.QUANTITY*P1.PRICE) FROM PART P1, ORDERS O1 WHERE P1.PARTNUM = O1.PARTNUM AND P1.PARTNUM = O.PARTNUM) PARTNUM SUM COUNT =========== =========== =========== 10 8400.00 4 23 4906.30 2 76 19610.00 5
AVG(O1.QUANTITY*P1.PRICE)Because of the correlation between the query and the subquery,
AND P1.PARTNUM = O.PARTNUMthis average is computed for every group of parts and then compared:
HAVING SUM(O.QUANTITY*P.PRICE) >
TIP: When using correlated subqueries with GROUP BY and HAVING, the columns in the HAVING clause must exist in either the SELECT clause or the GROUP BY clause. Otherwise, you get an error message along the lines of invalid column reference because the subquery is evoked for each group, not each row. You cannot make a valid comparison to something that is not used in forming the group.
SELECT NAME, ORDEREDON FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME ='TRUE WHEEL') NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996 BIKE SPEC 30-JUN-1996 BIKE SPEC 30-MAY-1996 BIKE SPEC 30-MAY-1996 BIKE SPEC 17-JAN-1996 LE SHOPPE 17-JAN-1996 LE SHOPPE 1-JUN-1996 AAA BIKE 1-JUN-1996 AAA BIKE 1-JUL-1996 AAA BIKE 1-JUL-1996 JACKS BIKE 11-JUL-1996
SELECT NAME, ORDEREDON FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME ='MOSTLY HARMLESS')
You could use EXISTS in this way to check on the existence of certain rows and control the output of your query based on whether they exist.
NOTE: Notice the use of SELECT * in the subquery inside the EXISTS. EXISTS does not care how many columns are returned.
If you use EXISTS in a correlated subquery, it is evaluated for every case implied by the correlation you set up. For example:
SELECT NAME, ORDEREDON FROM ORDERS O WHERE EXISTS (SELECT * FROM CUSTOMER C WHERE STATE ='NE' AND C.NAME = O.NAME) NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996 AAA BIKE 1-JUN-1996 AAA BIKE 1-JUL-1996 AAA BIKE 1-JUL-1996This slight modification of your first, uncorrelated query returns all the bike shops from Nebraska that made orders. The following subquery is run for every row in the query correlated on the CUSTOMER name and ORDERS name:
(SELECT * FROM CUSTOMER C WHERE STATE ='NE' AND C.NAME = O.NAME)
Closely related to EXISTS are the keywords ANY, ALL, and SOME. ANY and SOME are identical in function. An optimist would say this feature provides the user with a choice. A pessimist would see this condition as one more complication. Look at this query:
SELECT NAME, ORDEREDON FROM ORDERS WHERE NAME = ANY (SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL')
NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996
(SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL')Replacing ANY with SOME produces an identical result:
SELECT NAME, ORDEREDON FROM ORDERS WHERE NAME = SOME (SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL') NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996
SELECT NAME, ORDEREDON FROM ORDERS WHERE NAME IN (SELECT NAME FROM ORDERS WHERE NAME ='TRUE WHEEL') NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996
SELECT NAME, ORDEREDON FROM ORDERS WHERE NAME > ANY (SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE') NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996 LE SHOPPE 17-JAN-1996 LE SHOPPE 1-JUN-1996The answer is no. IN works like multiple equals. ANY and SOME can be used with other relational operators such as greater than or less than. Add this tool to your kit.
ALL returns TRUE only if all the results of a subquery meet the condition. Oddly enough, ALL is used most commonly as a double negative, as in this query:
SELECT NAME, ORDEREDON FROM ORDERS WHERE NAME <> ALL (SELECT NAME FROM ORDERS WHERE NAME ='JACKS BIKE') NAME ORDEREDON ========== =========== TRUE WHEEL 15-MAY-1996 TRUE WHEEL 19-MAY-1996 TRUE WHEEL 2-SEP-1996 TRUE WHEEL 30-JUN-1996 BIKE SPEC 30-JUN-1996 BIKE SPEC 30-MAY-1996 BIKE SPEC 30-MAY-1996 BIKE SPEC 17-JAN-1996 LE SHOPPE 17-JAN-1996 LE SHOPPE 1-JUN-1996 AAA BIKE 1-JUN-1996 AAA BIKE 1-JUL-1996 AAA BIKE 1-JUL-1996
A No, not really. Having so many ways to achieve the same result enables you to create some really neat statements. Flexibility is the virtue of SQL.
LE SHOPPE BIKE SPEC LE SHOPPE BIKE SPEC JACKS BIKE
2. Are the following statements true or false?
The aggregate functions SUM, COUNT, MIN,
MAX, and AVG all return multiple values.
The maximum number of subqueries that can be nested is two.
Correlated subqueries are completely self-contained.
3. Will the following subqueries work using the ORDERS table and the PART table?
SQL> SELECT * FROM PART; PARTNUM DESCRIPTION PRICE 54 PEDALS 54.25 42 SEATS 24.50 46 TIRES 15.25 23 MOUNTAIN BIKE 350.45 76 ROAD BIKE 530.00 10 TANDEM 1200.006 rows selected.INPUT/OUTPUT:
SQL> SELECT * FROM ORDERS;
ORDEREDON NAME PARTNUM QUANITY REMARKS 15-MAY-96 TRUE WHEEL 23 6 PAID 19-MAY-96 TRUE WHEEL 76 3 PAID 2-SEP-96 TRUE WHEEL 10 1 PAID 30-JUN-96 BIKE SPEC 54 10 PAID 30-MAY-96 BIKE SPEC 10 2 PAID 30-MAY-96 BIKE SPEC 23 8 PAID 17-JAN-96 BIKE SPEC 76 11 PAID 17-JAN-96 LE SHOPPE 76 5 PAID 1-JUN-96 LE SHOPPE 10 3 PAID 1-JUN-96 AAA BIKE 10 1 PAID 1-JUN-96 AAA BIKE 76 4 PAID 1-JUN-96 AAA BIKE 46 14 PAID 11-JUL-96 JACKS BIKE 76 14 PAID 13 rows selected.