There are constraints on the number of rows and columns that a subquery may return. If you use IN, ANY, or ALL, the subquery may return several rows, but only one column. If you use other operators, the subquery must return a single value.
A multiple-row subqueryTwo tables in the sample database are concerned with financial results. The fin_code table is a small table holding the different codes for financial data and their meanings:
To list the revenue items from the fin_data table, type the following:
SELECT * FROM fin_data WHERE fin_data.code IN [ SELECT fin_code.code FROM fin_code WHERE type = 'revenue' ]yearquartercodeamount1999Q1r110231999Q2r120331999Q3r129981999Q4r130142000Q1r13114
This example has used qualifiers to clearly identify the table to which the code column in each reference belongs. In this particular example, the qualifiers could have been omitted.
Two other keywords can be used as qualifiers for operators to allow them to work with multiple rows: ANY and ALL.
The following query is identical to the successful query above:
SELECT * FROM fin_data WHERE fin_data.code = ANY [ SELECT fin_code.code FROM fin_code WHERE type = 'revenue' ]
While the =ANY
condition is identical to the IN condition, ANY can also be used with inequalities such as to give more flexible use of subqueries.
The ALL keyword is similar to the word ANY. For example, the following query lists financial data that is not revenues:
SELECT * FROM fin_data WHERE fin_data.code ALL [ SELECT fin_code.code FROM fin_code WHERE type = 'revenue' ]
This is equivalent to the following command using NOT IN:
SELECT * FROM fin_data WHERE fin_data.code NOT IN [ SELECT fin_code.code FROM fin_code WHERE type = 'revenue' ]A common error using subqueries
In general, subquery result sets are restricted to a single column. The following example does not make sense because Adaptive Server Anywhere would not know which column from fin_code to compare to the fin_data.code column.
-- this query is incorrect SELECT * FROM fin_data WHERE fin_data.code IN [ SELECT fin_code.code, fin_code.type FROM fin_code WHERE type = 'revenue' ]Single-row subqueries
While subqueries used with an IN condition may return a set of rows, a subquery used with a comparison operator must return only one row. For example the following command results in an error since the subquery returns two rows:
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.