A subquery that follows a comparison operator (=, >, <, >=, <=, !=, <>, !>, !<) is called a comparison. The optimizer converts these subqueries to joins provided that the subquery:
returns exactly one value for each row of the main query.
does not contain a GROUP BY clause
does not contain the keyword DISTINCT
is not a UNION query
is not an aggregate query
Suppose the request "When were Suresh's products ordered, and by which sales representative?" were phrased as the subquery
SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE CustomerID = ( SELECT ID FROM Customers WHERE GivenName = 'Suresh' );
This query satisfies the criteria, and therefore, it would be converted to a query using a join:
SELECT OrderDate, SalesRepresentative FROM SalesOrders, Customers WHERE CustomerID=Customers.ID AND ( Surname = 'Clarke' OR GivenName = 'Suresh' );
However, the request, "Find the products whose in-stock quantities are less than double the average ordered quantity" cannot be converted to a join, as the subquery contains the AVG aggregate function:
SELECT Name, Description FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems );
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|