 Re: HAVING and WHERE clauses: Performance considerations in SQL
First of all, I hope that you know that fields are not anything like
columns and tables are not anything like files. Please tell me that
this was just an unfortunate bunch of data element names and not how
you think of data.
1) With the WHERE clause:
SELECT fld1, fld2, fld3
FROM MyFile
WHERE fld1 = 1
AND fld2 = 1
GROUP BY fld1, fld2, fld3;
The query is to act as if the WHERE clause is done first on the table
named (ugh!) MyFile. Then the resulting work set is grouped --
subsets of rows are reduced to a single row.
That is the model, but the optimizer is free to do this any way it
wants to do it. But since you violated the basic SQL Newsgroup
netiquette, and did not show us any DDL, we have no idea if there is
UNIQUE constraint on any of the columns, what the indexing is, etc.
But a smart optimizer will see the GROUP BY and that fld1 and fld2 are
constants, so we need "buckets" for the fld3 values. If fld3 is
indexed, then we know those values! That means looking at just the
index. Should be fast!
2) With the HAVING clause:
SELECT fld1, fld2, fld3
FROM MyFile
GROUP BY fld1, fld2, fld3
HAVING fld1 = 1
AND fld2 = 1;
This query is to act as if we make all possible groupings of (fld1,
fld2, fld3) in a working table, then applied the having clause.
That is the model, but the optimizer is free to do this any way it
wants to do it. But since you violated the basic SQL Newsgroup
netiquette, and did not show us any DDL, we have no idea if there is
UNIQUE constraint on any of the columns, what the indexing is, etc.
But a smart optimizer will see constants in the HAVING and convert
them into a WHERE clause. The two queries should work out about the
same.
| 
