Multi Row Subqueries
A multi row subquery returns 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.
