What’s the difference between a single-row subquery and a multiple-row subquery?

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.

Two 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' )

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 < or > 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' )

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' )

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.

What is a multiple row subquery?

What are the two types of subqueries?

What are the different types of a subquery?

Can we use multi row operation in single row subquery?