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

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:

• 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 );