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.
This query contains an example of a subquery comparison test:
SELECT Name, Description, Quantity FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems );
|Tee Shirt||Tank Top||28|
|Baseball Cap||Wool cap||12|
The following subquery retrieves a single value—the average quantity of items of each type per customer's order—from the SalesOrderItems table.
SELECT AVG( Quantity ) FROM SalesOrderItems;
Then the main query compares the quantity of each in-stock item to that value.
A subquery in a comparison test must return exactly one value. Consider this query, whose subquery extracts two columns from the SalesOrderItems table:
SELECT Name, Description, Quantity FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ), MAX( Quantity ) FROM SalesOrderItems);
It returns the error
Subquery allowed only one select list item.