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

Correlated subqueries Next Page

Converting subqueries in the WHERE clause to joins

The SQL Anywhere query optimizer converts some multi-level queries to use joins. 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 question "When did Mrs. Clarke and Suresh place their orders, and by which sales representatives?" can be written as a two-level query:

SELECT OrderDate, SalesRepresentative
FROM SalesOrders
WHERE CustomerID IN (
   FROM Customers
   WHERE Surname = 'Clarke' OR GivenName = 'Suresh' );

An alternate, and equally correct, way to write the query uses joins:

SELECT GivenName, Surname, OrderDate, SalesRepresentative
FROM SalesOrders, Customers
WHERE CustomerID=Customers.ID AND
  ( Surname = 'Clarke' OR GivenName = 'Suresh' );

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. Recall that when a subquery appears in the query's WHERE clause, it is of the form

SELECT select-list
FROM table
[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

Whether a subquery can be converted to a join depends on a number of factors, such as the type of operator and the structures of the query and of the subquery.