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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Subqueries » Introduction to subqueries

Introduction to subqueries Next Page

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.

Multiple row subqueries

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' );
Year Quarter Code Amount
1999 Q1 r1 1023
1999 Q2 r1 2033
1999 Q3 r1 2998
1999 Q4 r1 3014
2000 Q1 r1 3114

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' );
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 returns an error
SELECT *
FROM FinancialData
WHERE FinancialData.Code =
    ( SELECT FinancialCodes.Code
      FROM FinancialCodes
      WHERE type = 'revenue' );
Common errors using subqueries

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