Subqueries that can return only one or zero rows to the outer statement are called single-row subqueries. Single-row subqueries are subqueries used with a comparison operator in a WHERE, or HAVING clause.
Subqueries that can return more than one row (but only one column) to the outer statement are called multiple-row subqueries. Multiple-row subqueries are subqueries used with an IN, ANY, or ALL clause.
You store information particular to products in one table, Products, and information that pertains to sales orders in another table, SalesOrdersItems. The Products table contains the information about the various products. The SalesOrdersItems table contains information about customers' orders. If a company reorders products when there are fewer than 50 of them in stock, then it is possible to answer the question "Which products are nearly out of stock?" with this query:
SELECT ID, Name, Description, Quantity FROM Products WHERE Quantity < 50; |
However, a more helpful result would take into consideration how frequently a product is ordered, since having few of a product that is frequently purchased is more of a concern than having few product that is rarely ordered.
You can use a subquery to determine the average number of items that a customer orders, and then use that average in the main query to find products that are nearly out of stock. The following query finds the names and descriptions of the products which number less than twice the average number of items of each type that a customer orders.
SELECT Name, Description FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems ); |
In the WHERE clause, subqueries help select the rows from the tables listed in the FROM clause that appear in the query results. In the HAVING clause, they help select the row groups, as specified by the main query's GROUP BY clause, that appear in the query results.
The following example of a single-row subquery calculates the average price of the products in the Products table. The average is then passed to the WHERE clause of the outer query. The outer query returns the ID, Name, and UnitPrice of all products that are less expensive than the average:
SELECT ID, Name, UnitPrice FROM Products WHERE UnitPrice < ( SELECT AVG( UnitPrice ) FROM Products ) ORDER BY UnitPrice DESC; |
ID | Name | UnitPrice |
---|---|---|
401 | Baseball Cap | 10.00 |
300 | Tee Shirt | 9.00 |
400 | Baseball Cap | 9.00 |
500 | Visor | 7.00 |
501 | Visor | 7.00 |
Suppose you want to identify items that are low in stock, while also identifying orders for those items. You could execute a SELECT statement containing a subquery in the WHERE clause, similar to the following:
SELECT * FROM SalesOrderItems WHERE ProductID IN ( SELECT ID FROM Products WHERE Quantity < 20 ) ORDER BY ShipDate DESC; |
In this example, the subquery makes a list of all values in the ID column in the Products table, satisfying the WHERE clause search condition. The subquery then returns a set of rows, but only a single column. The IN keyword treats each value as a member of a set and tests whether each row in the main query is a member of the set.
Two tables in the SQL Anywhere sample database contain financial results data. The FinancialCodes table is a table holding the different codes for financial data and their meaning. To list the revenue items from the FinancialData table, execute the following query:
SELECT * FROM FinancialData WHERE Code IN ( SELECT Code FROM FinancialCodes WHERE type = 'revenue' ); |
Year | Quarter | Code | Amount |
---|---|---|---|
1999 | Q1 | r1 | 1023 |
1999 | Q2 | r1 | 2033 |
1999 | Q3 | r1 | 2998 |
1999 | Q4 | r1 | 3014 |
2000 | Q1 | r1 | 3114 |
... | ... | ... | ... |
The ANY and ALL keywords can be used in a similar manner. For example, the following query returns the same results as the previous query, but uses the ANY keyword:
SELECT * FROM FinancialData WHERE FinancialData.Code = ANY ( SELECT FinancialCodes.Code FROM FinancialCodes WHERE type = 'revenue' ); |
While the =ANY
condition is identical to the IN condition, ANY can also be used with inequalities such as <
or >
to give more flexible use of subqueries.
The ALL keyword is similar to the word ANY. For example, the following query lists financial data that is not revenue:
SELECT * FROM FinancialData WHERE FinancialData.Code <> ALL ( SELECT FinancialCodes.Code FROM FinancialCodes WHERE type = 'revenue' ); |
This is equivalent to the following command using NOT IN:
SELECT * FROM FinancialData WHERE FinancialData.Code NOT IN ( SELECT FinancialCodes.Code FROM FinancialCodes WHERE type = 'revenue' ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |