User's Guide
   PART 1. Working with Databases
     CHAPTER 7. 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)

Name	description	quantity
Tee Shirt	Tank Top	28
Baseball Cap	Wool cap	12
Visor	Cloth Visor	36
Visor	Plastic Visor	28
Sweatshirt	Hooded Sweatshirt	39
Sweatshirt	Zipped Sweatshirt	32

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

Similarly, this query returns multiple values from the quantity column - one for each row in the sales_order_items table.

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

It returns the error

subquery cannot return more than one result

The subquery must appear to the right of a comparison operator 

The subquery comparison test allows a subquery only on the right side of the comparison operator. Thus the comparison

main-query-expression < subquery

is allowed, but the comparison

subquery < main-query-expression

is not.