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 ); |
name | Description | Quantity |
---|---|---|
Tee Shirt | Tank Top | 28 |
Baseball Cap | Wool cap | 12 |
Visor | Cloth Visor | 36 |
Visor | Plastic Visor | 28 |
... | ... | ... |
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.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |