 |
|
Oracle Tips by Burleson |
Single Row Subquery
A single row subquery returns only one row. It
can be used with the equal comparison operators (=,<,>,<>, etc).
SQL> select
2 order_number
3 from
4 sales
5 where quantity = (select
6 max(quantity)
7 from
8 sales);
ORDER_NUMBER
--------------------
O161
Here, we get the order number for the order that
contains the max quantity value. The outer query can return multiply
rows, but the subquery can only return one row.
SQL> select
2 order_number
3 from
4 sales
5 where quantity > (select
6 avg(quantity)
7 from
8 sales);
ORDER_NUMBER
--------------------
O118
O149
O157
O161
O167
O179
O183
O186
O189
O193
O196
O197
O198
O199
14 rows
selected.
Here, we want the order numbers where the order
quantity is above the average quantity in the sales table. Notice
that the subquery returns one and only one row that used in the
filter.
Multi Row Subqueries
A multi row subqueryreturns one or more rows. Since it returns multiple values, the
query must use the set comparison operators (IN,ALL,ANY). If you use
a multi row subquery with the equals comparison operators, the
database will return an error if more than one row is returned. I am
looking for all the book_key
values that have been sold in South Carolina
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key = (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
store_key = (select
*
ERROR at line 6:
ORA-01427: single-row subquery returns more than one row
In the example above, the subquery returns
multiple rows, so the outer query could not evaluate the equals sign.
All I need to do is change the equals sign to a set operator.
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key in (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
BOOK_K
------
B111
B110
B103
B102
…
B116
B106
B102
26 rows selected.
The IN operator returns TRUE if the comparison
value is contained in the list; in this case, the results of the
subquery. The ANY and ALL operators work with the equal operators.
The ANY operator returns TRUE if the comparison value matches any of
the values in the list. The ALL operator returns TRUE only if the
comparison value matches all the values in the list.
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key = ANY (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
BOOK_K
------
B111
B110
…
B102
26 rows
selected.
As you can see, the =ANY comparison is the same as
the IN comparison.
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key = ALL (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
no rows
selected
Using the ALL operator in the above query will
return no rows, since the individual store keys cannot ever match all
the store keys in the list. With the IN operator, you can add the NOT
operator (NOT IN) to exclude values on the list as opposed to
including them.
The difference in single and multi row subqueries
is the operator you use in the comparison. Be careful with single row
subqueries. Sometimes you will get one row returned because of the
data you are developing your query with, but once the query is in use,
you may find that it can produce multiple rows, resulting in errors.
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by
John Garmany
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
