ASA SQL User's Guide
  Using Subqueries

Subquery comparison test

The subquery comparison test (=, <>, <. <=, >, >=) is a modified version of the simple comparison test. The only difference between the two is that in the former, the expression following the operator is a subquery. This test is used to compare a value from a row in the main query to a single value produced by the subquery.

Example 

This query contains an example of a subquery comparison test:

SELECT name, description, quantity
FROM product
WHERE quantity <  2 * (
   SELECT avg(quantity)
   FROM sales_order_items)

The following subquery retrieves a single value—the average quantity of items of each type per customer's order—from the sales_order_items table.

SELECT avg(quantity)
FROM sales_order_items

Then the main query compares the quantity of each in-stock item to that value.

A subquery in a comparison test returns one value 

A subquery in a comparison test must return exactly one value. Consider this query, whose subquery extracts two columns from the sales_order_items table:

SELECT name, description, quantity
FROM product
WHERE quantity <  2 * (
   SELECT avg(quantity), max (quantity)
   FROM sales_order_items)

It returns the error Subquery allowed only one select list item.