Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Single-row and multiple-row subqueries

There are constraints on the number of rows and columns that a subquery can 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 SQL Anywhere sample database contain financial results data. The FinancialCodes table is a table holding the different codes for financial data and their meaning. To list the revenue items from the FinancialData table, execute the following query:

SELECT *
FROM FinancialData
WHERE FinancialData.Code IN
    ( SELECT FinancialCodes.Code
        FROM FinancialCodes
        WHERE type = 'revenue' );

This example uses 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.

The ANY and ALL keywords can be used in a similar manner. For example, the following query returns the same results as the previous query, but uses the ANY keyword:

SELECT *
FROM FinancialData
WHERE FinancialData.Code = ANY
   (  SELECT FinancialCodes.Code
       FROM FinancialCodes
       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 revenue:

SELECT *
FROM FinancialData
WHERE FinancialData.Code <> ALL
   (  SELECT FinancialCodes.Code
       FROM FinancialCodes
      WHERE type = 'revenue' );

This is equivalent to the following command using NOT IN:

SELECT *
FROM FinancialData
WHERE FinancialData.Code NOT IN
   (  SELECT FinancialCodes.Code
      FROM FinancialCodes
      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:

-- this query returns an error
SELECT *
FROM FinancialData
WHERE FinancialData.Code =
    ( SELECT FinancialCodes.Code
      FROM FinancialCodes
      WHERE type = 'revenue' );

In general, subquery result sets are restricted to a single column. The following example does not make sense because SQL Anywhere does not know which column from FinancialCodes to compare to the FinancialData.Code column.

-- this query returns an error
SELECT *
FROM FinancialData
WHERE FinancialData.Code IN
    ( SELECT FinancialCodes.Code, FinancialCodes.type
      FROM FinancialCodes
      WHERE type = 'revenue' );