Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the intended result. In general, that means eliminating undesired rows in earlier clauses.
To list all sales reps with more than 55 orders and an ID of more than 1000, enter the following statement.
SELECT SalesRepresentative, COUNT( * ) FROM SalesOrders WHERE SalesRepresentative > 1000 GROUP BY SalesRepresentative HAVING count( * ) > 55 ORDER BY SalesRepresentative;
The following statement produces the same results.
SELECT SalesRepresentative, COUNT( * ) FROM SalesOrders GROUP BY SalesRepresentative HAVING count( * ) > 55 AND SalesRepresentative > 1000 ORDER BY SalesRepresentative;
SQL Anywhere detects that both statements describe the same result set, and so executes each efficiently.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|