Contents Index Restricting groups Selecting Data Using Subqueries

ASA Getting Started
  Selecting Aggregate Data
    Restricting groups

Combining WHERE and HAVING clauses


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 type and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

Example 

To list all sales reps with more than 55 orders and an ID of more than 1000, type the following statement.

SELECT sales_rep, count( * )
FROM sales_order KEY JOIN employee
WHERE sales_rep > 1000
GROUP BY sales_rep
HAVING count( * ) > 55
ORDER BY sales_rep

The following statement produces the same results.

SELECT sales_rep, count( * )
FROM sales_order KEY JOIN employee
GROUP BY sales_rep
HAVING count( * ) > 55 AND sales_rep > 1000
ORDER BY sales_rep

Adaptive Server Anywhere detects that both statements describe the same result set, and so executes each efficiently.


Contents Index Restricting groups Selecting Data Using Subqueries