A subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together. By the end of the day, you will understand and be able to do the following:
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.
Simply put, a subquery lets you tie the result set of one query to another. The general syntax is as follows:
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
The tables share a common field called PARTNUM. Suppose that you didn't know (or didn't want to know) the PARTNUM, but instead wanted to work with the description of the part. Using a subquery, you could type this:
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
Even better, if you use the concepts you learned on Day 6, you could enhance the PARTNUM column in the result by including the DESCRIPTION, making PARTNUM clearer for anyone who hasn't memorized it. Try 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%") 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
The first part of the query is very familiar:
SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS FROM ORDERS O, PART P
Here 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.PARTNUM
is 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.99
Suppose 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 select
and 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
77
You would take this result and apply it to O.PARTNUM =, which is the step that causes the problem.
How can PARTNUM be equal to both 76 and 77? This must be what the engine meant when it accused you of being a simpleton. When you used the LIKE clause, you opened yourself up for this error. When you combine the results of a relational operator with another relational operator, such as =, <, or >, you need to make sure the result is singular. In the case of the example we have been using, the solution would be to rewrite the query using an = instead of the LIKE, like 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 = "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
This subquery returns only one unique result; therefore narrowing your = condition to a single value. How can you be sure the subquery won't return multiple values if you are looking for only one value?
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.
The aggregate functions SUM, COUNT, MIN, MAX, and AVG all return a single value. To find the average amount of an order, type this:
SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM
AVG
===========
2419.16
This statement returns only one value. To find out which orders were above average, use the preceding SELECT statement for your subquery. The complete query and result are as follows:
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
This example contains a rather unremarkable SELECT/FROM/WHERE clause:
SELECT O.NAME, O.ORDEREDON, O.QUANTITY * P.PRICE TOTAL FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM
These 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.
Nesting is the act of embedding a subquery within another subquery. For example:
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 NONE
with 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
Here's a look at what you asked for. In the innermost set of parentheses, you find a familiar statement:
SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM
This 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
We just spent some time discussing why your subqueries should return just one value. The reason this query was able to return more than one value becomes apparent in a moment.
You bring these results to the statement:
SELECT C.NAME, C.ADDRESS, C.STATE,C.ZIP FROM CUSTOMER C WHERE C.NAME IN
(...)
The first two lines are unremarkable. The third reintroduces the keyword IN, last seen on Day 2, "Introduction to the Query: The SELECT Statement." IN is the tool that enables you to use the multiple-row output of your subquery. IN, as you remember, looks for matches in the following set of values enclosed by parentheses, which in the this case produces the following values:
LE SHOPPE BIKE SPEC LE SHOPPE BIKE SPEC JACKS BIKE
This 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 34567
This 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
Let's examine this query in the order the SQL engine would. First, look at the subquery:
SELECT AVG(QUANTITY)
FROM ORDERS
AVG
===========
6
By 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 5
When 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
The subqueries you have written so far are self-contained. None of them have used a reference from outside the subquery. Correlated subqueries enable you to use an outside reference with some strange and wonderful results. Look at the following query:
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 PAID
This 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
In fact, except for the order, the results are identical. The correlated subquery acts very much like a join. The correlation is established by using an element from the query in the subquery. In this example the correlation was established by the statement
WHERE P.PARTNUM = O.PARTNUM
in 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 PAID
is processed against the subquery criteria:
SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM
This operation returns the DESCRIPTION of every row in PART where P.PARTNUM = O.PARTNUM. These descriptions are then compared in the WHERE clause:
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)
This SELECT caused a General Protection Fault on my Windows operating system. The SQL engine simply can't correlate all the columns in PART with the operator =.
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
The subquery does not just compute one
AVG(O1.QUANTITY*P1.PRICE)
Because of the correlation between the query and the subquery,
AND P1.PARTNUM = O.PARTNUM
this 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.
The usage of the keywords EXISTS, ANY, and ALL is not intuitively obvious to the casual observer. EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything and FALSE if the result set is empty. For example:
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
Not what you might expect. The subquery inside EXISTS is evaluated only once in this uncorrelated example. Because the return from the subquery has at least one row, EXISTS evaluates to TRUE and all the rows in the query are printed. If you change the subquery as shown next, you don't get back any results.
SELECT NAME, ORDEREDON FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME ='MOSTLY HARMLESS')
EXISTS evaluates to FALSE. The subquery does not generate a result set because MOSTLY HARMLESS is not one of your names.
NOTE: Notice the use of SELECT * in the subquery inside the EXISTS. EXISTS does not care how many columns are returned.
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.
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-1996
This 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)
EXISTS is TRUE for those rows that have corresponding names in CUSTOMER located in NE. Otherwise, it returns FALSE.
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
ANY compared the output of the following subquery to each row in the query, returning TRUE for each row of the query that has a result from the subquery.
(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
You may have already noticed the similarity to IN. The same query using IN is as follows:
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
As you can see, IN returns the same result as ANY and SOME. Has the world gone mad? Not yet. Can IN do this?
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-1996
The 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
This statement returns everybody except JACKS BIKE. <>ALL evaluates to TRUE only if the result set does not contain what is on the left of the <>.
Today you performed dozens of exercises involving subqueries. You learned how to use one of the most important parts of SQL. You also tackled one of the most difficult parts of SQL: a correlated subquery. The correlated subquery creates a relationship between the query and the subquery that is evaluated for every instance of that relationship. Don't be intimidated by the length of the queries. You can easily examine them one subquery at a time.
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.
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
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.
b. SQL> SELECT PARTNUM
FROM ORDERS
WHERE PARTNUM =
(SELECT * FROM PART
WHERE DESCRIPTION = 'LE SHOPPE');
c. SQL> SELECT NAME, PARTNUM
FROM ORDERS
WHERE EXISTS
(SELECT * FROM ORDERS
WHERE NAME = 'TRUE WHEEL');
© Copyright, Macmillan Computer Publishing. All rights reserved.