A nested subquery is a subquery nested within another subquery. There is no limit to the level of subquery nesting you can define, however, queries with three or more levels take considerably longer to run than do smaller queries.
The following example uses nested subqueries to determine the order IDs and line IDs of those orders shipped on the same day when any item in the fees department was ordered.
SELECT ID, LineID FROM SalesOrderItems WHERE ShipDate = ANY ( SELECT OrderDate FROM SalesOrders WHERE FinancialCode IN ( SELECT Code FROM FinancialCodes WHERE ( Description = 'Fees' ) ) ); |
ID | LineID |
---|---|
2001 | 1 |
2001 | 2 |
2001 | 3 |
2002 | 1 |
... | ... |
In this example, the innermost subquery produces a column of financial codes whose descriptions are "Fees":
SELECT Code FROM FinancialCodes WHERE ( Description = 'Fees' ); |
The next subquery finds the order dates of the items whose codes match one of the codes selected in the innermost subquery:
SELECT OrderDate FROM SalesOrders WHERE FinancialCode IN ( subquery-expression ); |
Finally, the outermost query finds the order IDs and line IDs of the orders shipped on one of the dates found in the subquery.
SELECT ID, LineID FROM SalesOrderItems WHERE ShipDate = ANY ( subquery-expression ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |