Which operator can be used with a multiple row subquery?

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

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