Choosing Between HAVING and WHERE
WHERE puts conditions on the table rows, determining which should be returned. HAVING puts conditions on grouped result rows. The processing order is as follows:
Select rows with WHERE.
Divide rows into sets with GROUP BY.
Calculate aggregate values for each group.
Eliminate unwanted group result rows with HAVING.
Any rows you can remove with WHERE, rather than HAVING, make your query more efficient. There are fewer rows to group and fewer to aggregate. In Figure 6–18 it makes sense to remove books before rather than after grouping and counting. You save a lot of work.
)
Figure 6–18. WHERE and HAVING
Use HAVING to limit group result rows, as in the following query:
Adaptive Server Anywhere select type, count(*) from product group by type having count(*) > 5 type count(*) ============ =========== application 8 [1 row]