The query optimizer automatically rewrites as joins many of the queries that make use of subqueries. The conversion is performed without any user action. This section describes which subqueries can be converted to joins so you can understand the performance of queries in your database.
The criteria that must be satisfied in order for a multi-level query to be able to be rewritten with joins differ for the various types of operators, and the structures of the query and of the subquery. Recall that when a subquery appears in the query's WHERE clause, it is of the form
SELECT select-list FROM table WHERE [NOT] expression comparison-operator ( subquery-expression ) | [NOT] expression comparison-operator { ANY | SOME } ( subquery-expression ) | [NOT] expression comparison-operator ALL ( subquery-expression ) | [NOT] expression IN ( subquery-expression ) | [NOT] EXISTS ( subquery-expression ) GROUP BY group-by-expression HAVING search-condition
For example, consider the request, "When did Mrs. Clarke and Suresh place their orders, and by which sales representatives?" It can be answered with the following query:
SELECT OrderDate, SalesRepresentative FROM SalesOrders WHERE CustomerID IN ( SELECT ID FROM Customers WHERE Surname = 'Clarke' OR GivenName = 'Suresh' ); |
OrderDate | SalesRepresentative |
---|---|
2001-01-05 | 1596 |
2000-01-27 | 667 |
2000-11-11 | 467 |
2001-02-04 | 195 |
... | ... |
The subquery yields a list of customer IDs that correspond to the two customers whose names are listed in the WHERE clause, and the main query finds the order dates and sales representatives corresponding to those two people's orders.
The same question can be answered using joins. Here is an alternative form of the query, using a two-table join:
SELECT OrderDate, SalesRepresentative FROM SalesOrders, Customers WHERE CustomerID=Customers.ID AND ( Surname = 'Clarke' OR GivenName = 'Suresh' ); |
This form of the query joins the SalesOrders table to the Customers table to find the orders for each customer, and then returns only those records for Suresh and Clarke.
There are cases where a subquery works but a join does not. For example:
SELECT Name, Description, Quantity FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems ); |
name | Description | Quantity |
---|---|---|
Tee Shirt | Tank Top | 28 |
Baseball Cap | Wool cap | 12 |
Visor | Cloth Visor | 36 |
... | ... | ... |
In this case, the inner query is a summary query and the outer query is not, so there is no way to combine the two queries by a simple join.
Subquery that follows a comparison operator
Subquery that follows ANY, ALL or SOME
Subquery that follows IN
Subquery that follows EXISTS
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |