Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Subqueries

Subqueries and joins Next Page

Nested subqueries


As you have seen, subqueries usually appear in the HAVING clause or the WHERE clause of a query. A subquery may itself contain a WHERE clause and/or a HAVING clause, and, consequently, a subquery may appear in another subquery. Subqueries inside other subqueries are called nested subqueries.

Examples

List 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
... ...
Explanation of the nested subqueries
SELECT Code
FROM FinancialCodes
WHERE ( Description = 'Fees' );
SELECT OrderDate
FROM SalesOrders
WHERE FinancialCode 
IN ( subquery-expression );
SELECT ID, LineID
FROM SalesOrderItems
WHERE ShipDate = ANY ( subquery-expression );

Nested subqueries can also have more than three levels. Though there is no maximum number of levels, queries with three or more levels take considerably longer to run than do smaller queries.