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 » How subqueries work

Converting subqueries in the WHERE clause to joins Next Page

Comparison operators

A subquery that follows a comparison operator (=, <>, <, <=, >, >=) must satisfy certain conditions if it is to be converted into a join. For example, subqueries that follow comparison operators are valid only if they return exactly one value for each row of the main query. In addition to this criterion, a subquery is converted to a join only if the subquery:


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 = (
   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 );