ASA Getting Started
  Selecting Data Using Subqueries

Single-row and multiple-row subqueries

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 subquery 

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

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:

-- this query is incorrect
SELECT *
FROM fin_data
WHERE fin_data.code =
   (   SELECT fin_code.code
       FROM fin_code
       WHERE type = 'revenue' )