First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 17. Introduction to Subqueries       

Comparisons using subqueries

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 contents of the fin_code table:

1. Type the following:

   SELECT *
   FROM fin_code

code	type	description
e1	expense	Fees
e2	expense	Services
e3	expense	Sales & Marketing
e4	expense	R&D
e5	expense	Administration
r1	revenue	Fees
r2	revenue	Services

The fin_data table holds financial data for each financial code for each quarter.

  To list the contents of the fin_data table:

1. Type the following:

   SELECT *
   FROM fin_data

year	quarter	code	amount
1992	Q1	e1	101
1992	Q1	e2	403
1992	Q1	e3	1437
1992	Q1	e4	623
1992	Q1	e5	381

The following query uses a subquery to list just the revenue items from the fin_data table.

  To list the revenue items from the fin_data table:

1. Type the following:

   SELECT *
   FROM fin_data
   WHERE fin_data.code IN
       (   SELECT fin_code.code
         FROM fin_code
         WHERE type = 'revenue' )

year	quarter	code	amount
1992	Q1	r1	1023
1992	Q2	r1	2033
1992	Q3	r1	2998
1992	Q4	r1	3014
1993	Q1	r1	3114

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.

Notes about subqueries 

Subqueries are restricted to one column name listed between SELECT and FROM: one select-list item. The following example does not make sense, since SQL would not know which column from fin_code to compare to the fin_data.code column.

SELECT *
FROM fin_data
WHERE fin_data.code IN
   (   SELECT fin_code.code, fin_code.type
      FROM fin_code
      WHERE type = 'revenue' )

Further, while subqueries used with an IN condition may return several 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:

SELECT *
FROM fin_data
WHERE fin_data.code =
   (   SELECT fin_code.code
      FROM fin_code
      WHERE type = 'revenue' )

The IN comparison allows several rows. 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 word ALL 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' )