A subquery that follows one of the keywords ALL, ANY and SOME is called a quantified comparison. The optimizer converts these subqueries to joins provided that:
The main query does not contain a GROUP BY clause, and is not an aggregate query, or the subquery returns exactly one value.
The subquery does not contain a GROUP BY clause.
The subquery does not contain the keyword DISTINCT.
The subquery is not a UNION query.
The subquery is not an aggregate query.
The conjunct 'expression comparison-operator { ANY | SOME } ( subquery-expression )' must not be negated.
The conjunct 'expression comparison-operator ALL ( subquery-expression )' must be negated.
The first four of these conditions are relatively straightforward.
The request "When did Ms. Clarke and Suresh place their orders, and by which sales representatives?" can be handled in subquery form:
SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE CustomerID = ANY ( SELECT ID FROM Customers WHERE Surname = 'Clarke' OR GivenName = 'Suresh' ); |
Alternately, it can be phrased in join form
SELECT OrderDate, SalesRepresentative FROM SalesOrders, Customers WHERE CustomerID=Customers.ID AND ( Surname = 'Clarke' OR GivenName = 'Suresh' ); |
However, the request, "When did Ms. Clarke, Suresh, and any employee who is also a customer, place their orders?" would be phrased as a union query, and cannot be converted to a join:
SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE CustomerID = ANY ( SELECT ID FROM Customers WHERE Surname = 'Clarke' OR GivenName = 'Suresh' UNION SELECT EmployeeID FROM Employees ); |
Similarly, the request "Find the order IDs and customer IDs of those orders not shipped after the first shipping dates of all the products" would be phrased as the aggregate query, and therefore cannot be converted to a join:
SELECT ID, CustomerID FROM SalesOrders WHERE NOT OrderDate > ALL ( SELECT FIRST ( ShipDate ) FROM SalesOrderItems ORDER BY ShipDate ); |
The fifth criterion is a little more puzzling. Queries taking the following form are converted to joins:
SELECT select-list FROM table WHERE NOT expression comparison-operator ALL ( subquery-expression )
SELECT select-list FROM table WHERE expression comparison-operator ANY ( subquery-expression )
However, the following queries are not converted to joins:
SELECT select-list FROM table WHERE expression comparison-operator ALL ( subquery-expression )
SELECT select-list FROM table WHERE NOT expression comparison-operator ANY ( subquery-expression )
The first two queries are equivalent, as are the last two. Recall that the ANY operator is analogous to the OR operator, but with a variable number of arguments; and that the ALL operator is similarly analogous to the AND operator. For example, the following two expressions are equivalent:
NOT ( ( X > A ) AND ( X > B ) ) ( X <= A ) OR ( X <= B ) |
The following two expressions are also equivalent:
WHERE NOT OrderDate > ALL ( SELECT FIRST ( ShipDate ) FROM SalesOrderItems ORDER BY ShipDate ) |
WHERE OrderDate <= ANY ( SELECT FIRST ( ShipDate ) FROM SalesOrderItems ORDER BY ShipDate ) |
In general, the following expressions are equivalent:
NOT column-name operator ANY ( subquery-expression )
column-name inverse-operator ALL ( subquery-expression )
These expressions are generally equivalent as well:
NOT column-name operator ALL ( subquery-expression )
column-name inverse-operator ANY ( subquery-expression )
where inverse-operator is obtained by negating operator, as shown in the table below:
operator | inverse-operator |
---|---|
= | <> |
< | => |
> | =< |
=< | > |
=> | < |
<> | = |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |