Online Tutorials & Training Materials | STechies.com
Register Login

Which operator can be used with a multiple row subquery?

Shalesh Singh Visen || 06 Mar 2008 3:21 pm || 0

Q. Which operator can be used with a multiple-row subquery?

A. =
B. LIKE
C. BETWEEN
D. NOT IN
E. Is
F. <>
Answer: D

Q. What are the uses of IN, ANY, and ALL in Multiple-row subqueries?

Ans

For example, there are two tables in the SQL Anywhere sample database contain Accounting results data. The AccountCodes table is a table which contians various codes for Accounting data and their meaning. Therefore in order to list the revenue items from the AccountingData table, please execute the following query:

SELECT *
FROM AccountingData
WHERE Code IN
    ( SELECT Code
        FROM AccountCodes
        WHERE type = 'revenue' );
Year Quarter  Code  Amount
2011  Q1 r1 2123
2011 Q2  r1 3154
2011 Q3 r1 4020
2011 Q4    r1 4875
2012  Q1  r1 5134

The ANY and ALL are used in a similar manner. 

For example, the following query below returns the same results as, but we are using ANY keyword:

SELECT *
FROM AccountingData
WHERE AccountingData.Code = ANY
   (  SELECT AccountCodes.Code
       FROM AccountCodes
       WHERE type = 'revenue' );

While the =ANY condition is as same as IN condition, But condition ANY can be also used with inequalities like < or > for more flexible use of subqueries.

The ALL keyword is same as ANY keyword.

The following query below lists Accounting data which is not revenue:

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

This above is same as the following command given via using NOT IN:

SELECT *
FROM AccountingData
WHERE AccountingData.Code NOT IN
   (  SELECT AccountCodes.Code
      FROM AccountCodes
      WHERE type = 'revenue' );