The optimizer converts a subquery that follows the EXISTS keyword only if:
The main query does not contain a GROUP BY clause, and is not an aggregate query, or the subquery returns exactly one value.
The conjunct 'EXISTS (subquery)' is not negated.
The subquery is correlated; that is, it contains an outer reference.
The request, "Which customers placed orders after July 13, 2001?", which can be formulated by a query whose non-negated subquery contains the outer reference Customers.ID = SalesOrders.CustomerID, can be represented with the following join:
SELECT GivenName, Surname FROM Customers WHERE EXISTS ( SELECT * FROM SalesOrders WHERE ( OrderDate > '2001-07-13' ) AND ( Customers.ID = SalesOrders.CustomerID ) );
The EXISTS keyword tells the database server to check for empty result sets. When using inner joins, the database server automatically displays only the rows where there is data from all the tables in the FROM clause. So, this query returns the same rows as does the one with the subquery:
SELECT DISTINCT GivenName, Surname FROM Customers, SalesOrders WHERE ( SalesOrders.OrderDate > '2001-07-13' ) AND ( Customers.ID = SalesOrders.CustomerID );
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|